Formula and function

Copper Contributor

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 NAME5/14/2023PRODUCT NAMESJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
MATERIAL ACalculation.JPG?MATERIAL A101102103104105106107108109110111112
MATERIAL B?MATERIAL B201202203204205206207208209210211212
MATERIAL C?MATERIAL C301302303304305306307308309310311312
EXCHANGE RATE?EXCHANGE RATE401402403404405406407408409410411412
4 Replies

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),


If older Excel, it can be done using hlookup.

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.
best response confirmed by urb1985 (Copper Contributor)


In B2:


Fill down.

This is working perfectly!

Really Thankful.