Formula and function

[urb1985]

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	PRODUCT NAMES	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
MATERIAL A	?	MATERIAL A	101	102	103	104	105	106	107	108	109	110	111	112
MATERIAL B	?	MATERIAL B	201	202	203	204	205	206	207	208	209	210	211	212
MATERIAL C	?	MATERIAL C	301	302	303	304	305	306	307	308	309	310	311	312
EXCHANGE RATE	?	EXCHANGE RATE	401	402	403	404	405	406	407	408	409	410	411	412

[#2]

@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.

[#3]

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.

[#4 · Accepted solution]

@urb1985 

In B2:

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

Fill down.

[#5]

This is working perfectly!

Really Thankful.