Forum Discussion

urb1985's avatar
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 NAME5/14/2023PRODUCT NAMESJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
MATERIAL A?MATERIAL A101102103104105106107108109110111112
MATERIAL B?MATERIAL B201202203204205206207208209210211212
MATERIAL C?MATERIAL C301302303304305306307308309310311312
EXCHANGE RATE?EXCHANGE RATE401402403404405406407408409410411412
excel
  • bosinander's avatar
    bosinander
    Steel 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.

    • urb1985's avatar
      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.

Resources