How to lookup data from the last column of a given range?

[sammyshah2003]

Hello Excel experts,

I wonder how do I use lookup formula (Vlookup) to read the data from a range of table where the data increases every month in next column within the range.

Refer to the attached file. In sheet 1 I need to capture the exchange rates which are in sheet 2. However, I want to give a dynamic formula so that it takes the exchange rates from the "LAST UPDATED" column.

Please help.
Sammy

[jason.b75]

Something like

=VLOOKUP($C3,'Exchange rates'!$B$5:$O$14,MATCH(1E+100,'Exchange rates'!$B$5:$O$5),FALSE)

Try to avoid using full column references in your formulas when you only have a few rows of data, doing that is what causes the noticeable delay when you enter anything into the sheet.

[FDibbins]

Or this assuming you will always have all the values in row 5 filled in...
=VLOOKUP(C3,'Exchange rates'!$B$5:$Z$14,COUNTA('Exchange rates'!$B$5:$Z$5),0)

[protonLeah]

Convert the exchange rates to a table, then use:

Please Login or Register  to view this content.

[alansidman]

In D2, set data validation to the month values in the lookup sheet. D4:O4

In D3 type =VLOOKUP(C3,'Exchange rates'!$B$5:$O$14,MATCH(Data!$D$2,'Exchange rates'!$D$4:$O$4,0)+2,TRUE)
and copy down in column D