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.