Forum Discussion
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 attached below.
Can anyone help?
Thank you in advance!
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)))
- PS I am using the MS 365 version of Excel
That could be like
=LET( FXrate, VSTACK(A2:B8, C2:D9), DateInput, A19:A20, XLOOKUP(DateInput, CHOOSECOLS( FXrate, 1), CHOOSECOLS( FXrate, 2) ) )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)))- Sergei and dscheikey -
Both your solutions work beautifully.....but of course you knew that 🙂 !
Thank you so much for your lightening fast replies - much appreciated.
I will now hopefully learn how your solutions work as I adapt them to my live data.
Cheers
Mike
Pretty much the same as SergeiBaklan except that I resurrected the old LOOKUP function
= LET( FXrate, VSTACK(_2020, _2021), LOOKUP(dateInput, FXrate) )If you do not want to return the n/a then either LOOKUP or XLOOKUP can return a prior available value
= LET( FXrate, VSTACK(_2020, _2021), rate, TAKE(FXrate,,-1), date, TAKE(FXrate,,1)/ISNUMBER(rate), LOOKUP(dateInput, date, rate) )Thanks for everybody's input.
After getting a better understanding of Sergei's and dscheikey's solutions, I used dscheikey's because it avoids the need to add references to specific XLOOKUP arrays for each year.
I added the use of INDIRECT to reference the DateInput cell (for me always 1 or 2 cells left of the formula) to reduce potential for mishaps when copy/pasting the formula. I also changed the name of dscheikey's "in" to "YEARCOL" for my easier understanding.
My inexperience with most of the functions used required quite a bit of digging to understand the suggested solutions, so I documented Sergie's, dscheikey's and my formulas just so I won't be totally confused when I see these formulas a year from now
- so I share my pedantic formula explanations (attached "...FINAL.xslx") in case other equally inexperienced users might find this type of formula useful.BTW Peter - I decided "#N/A" was an adequate result if something when goes wrong when using my "final" formula. Thanks anyway.
see comment in previous message
