Forum Discussion
Solved
Complex (for me!) lookup formula - help please....
I need an Excel formula to return a lookup value based on date. The lookup dates and values (exchange rates for each date) are arranged in two columns per year. More detail in the spreadsheet attach...
Please try this:
=LET(in,XMATCH(YEAR(A19),$1:$1),XLOOKUP(A19,OFFSET($A$1,1,in-1,365),OFFSET($A$1,1,in,365)))
That could be like
=LET(
FXrate, VSTACK(A2:B8, C2:D9),
DateInput, A19:A20,
XLOOKUP(DateInput, CHOOSECOLS( FXrate, 1), CHOOSECOLS( FXrate, 2) )
)