Forum Discussion

urb1985

Copper Contributor

Aug 03, 2023

Solved

Formula and function

I am looknig for a formula where only the date change can pick the correspondant figure from the table for a particular material at a particular cell. example below; PRODUCT NAME 5/14/2023 P...

excel

HansVogelaar
Aug 03, 2023
urb1985

In B2:

=INDEX(E2:O2,MATCH($B$1-DAY($B$1)+1,$E$1:$O$1,0))

Fill down.

bosinander

Iron Contributor

Aug 03, 2023

urb1985
Row4: B2 has the day number (14) subtracted to find the same figure as the month.

Months may be formatted as in your example to not show the day (-01 with my local settings).

=LET(months,     $E$1:$O$1,
     figures,    E2:O2,
     lookupDate, $B$1,
lookupMonth,     lookupDate-DAY(lookupDate)+1,
output,          XLOOKUP(lookupMonth,months,figures),
output
)

If older Excel, it can be done using hlookup.

Formula and function (xlookup date in months).xlsx11 KB

urb1985

Copper Contributor

Aug 03, 2023

Thank you Bosinander for your help however unfortunately the provided formulas not working in my office, i am using Microsoft office 2013, can you drive formula for that version? many thanks.

HansVogelaar
MVP
Aug 03, 2023
urb1985

In B2:

=INDEX(E2:O2,MATCH($B$1-DAY($B$1)+1,$E$1:$O$1,0))

Fill down.
- urb1985
  Copper Contributor
  Aug 03, 2023
  This is working perfectly!
  
  Really Thankful.