Complex (for me!) lookup formula - help please....

[Mike_OBree]

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!

[#2]

PS I am using the MS 365 version of Excel

[#3]

@Mike_OBree 

That could be like

=LET(
  FXrate, VSTACK(A2:B8, C2:D9),
  DateInput, A19:A20,
  XLOOKUP(DateInput, CHOOSECOLS( FXrate, 1), CHOOSECOLS( FXrate, 2) )
)

[#4 · Accepted solution]

@Mike_OBree 

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

[#5]

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

[#6]

@Mike_OBree 

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

[#7]

@PeterBartholomew1 

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.

[#8]

see comment in previous message

[#9]

@Mike_OBree 

Hi Mike, the only two mistakes I can recognize are

1) that you have mixed up the names. The solution with offset where you don't have to name the arrays separately is mine.

2) Also, there are no columns for 2025 in the 'fx rates 2002 to date' table.

I would probably have used XLOOKUP() and OFFSET() instead of INDIRECT() and IF():

C29 = LET(InputDate,XLOOKUP(TRUE,OFFSET(C29,0,-2,1,2)>0,OFFSET(C29,0,-2,1,2)),YEARCOL,XMATCH(YEAR(InputDate),'fx rates 2002 to date'!$2:$2),XLOOKUP(InputDate,OFFSET('fx rates 2002 to date'!$A$2,1,YEARCOL-1,365),OFFSET('fx rates 2002 to date'!$A$2,1,YEARCOL,365)))

[#10]

dscheikey -
1) You are right on, I mixed up the names everywhere (even for "Best Response")- so sorry ... I will try to rectify that!
2) You are right ... there are no columns for 2025 in the table... but I did that on purpose just to ensure I would get a "#N/A" value when appropriate.

I'll take a look at your "XLOOKUP() and OFFSET() instead of INDIRECT() and IF()" variation:
thanks again.

[#11]

@dscheikey 

dscheikey -

Very slick use of XLOOKUP and OFFSET to reference the InputDate Cell !

However, if you copy/paste the Cell with the formula included in it to another excel file, although the "C29" reference will adjust accordingly, the sheet name 'fx rates 2002 to date will now have the originating file name appended to it.
On the other hand, if you copy the formula from the cell edit window, the paste correctly keeps just the 'fx rates 2002 to date' sheet name (without the wrong file name), but then the 'C29' will not adjust appropriately.
My version using "INDIRECT" did not suffer from that problem.
Does this make any sense?

[#12]

@Mike_OBree 

Yes, that makes sense! If you copy the formula to another worksheet, it is best to do this via the edit bar. Then you have to adjust the worksheet names and cell C29 once. That was just an example. So adjust the C29 by the cell in which the formula is located. If you use it in C1, you must replace C29 with C1.

But the Indirect() also works.