Lookup value based on nearest date & column heading in another table

[Gwill1983]

Hi all,

Tricky one to describe, but I will try my best.

I am trying to create an exchange rate table that looks up the exchange rate based on a date that the order was placed. I have my very rough tables below:

In cell B2, I would like to be able to lookup the exchange rate for the currency added into C2 (which will match one of the column headings in the 2nd table) within the 2nd table returning the value in the table based on the date closest in the past to the date in A2.

I am really stumped by this one, so any help is much appreciated!

 

[Gwill1983]

I have solved this myself!

=IF([@Currency]="Pounds",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,2,0),IF([@Currency]="Euro",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,3,0),IF([@Currency]="Yen",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,4,0),IF([@Currency]="Dollar",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,5,0)))))

 

[Peter_SSs]

I have solved this myself!

Glad you figured something out.

Here are a couple of shorter options (columns C & D) that seem to produce the same results as your formula (column B)

Gwill1983.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	date of booking	exchange rate	exchange rate 2	exchange rate 3	Currency			Date	Pounds	Euro	Yen	Dollar
2	1/01/2022	#N/A	#N/A	#N/A	Yen			1/02/2022	1	10	100	1000
3	6/04/2022	1	1	1	Pounds			1/06/2022	2	20	200	2000
4	3/09/2022	30	30	30	Euro			1/08/2022	3	30	300	3000
5	2/12/2022	3000	3000	3000	Dollar			1/01/2023	4	40	400	4000
6	10/01/2023	400	400	400	Yen
7
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IF([@Currency]="Pounds",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,2,0),IF([@Currency]="Euro",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,3,0),IF([@Currency]="Yen",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,4,0),IF([@Currency]="Dollar",VLOOKUP(MAX((Table3[Date]<[@[date of booking]])*Table3[Date]),Table3,5,0)))))
C2:C6	C2	=VLOOKUP([@[date of booking]],CHOOSECOLS(Table3,1,MATCH([@Currency],Table3[#Headers],0)),2)
D2:D6	D2	=XLOOKUP([@[date of booking]],Table3[Date],INDIRECT("Table3["&[@Currency]&"]"),,-1)