VLOOKUP and HLOOKUP within one formula to find data [Stimulating but not too hard]

Hey all,

I have a VLOOKUP embedded within a HLOOKUP and I am sort of confused how to do this. It is nothing too hard if your efficient with Excel. I have attached the spreadsheet so you can follow my explanation or else it will be confusing.

Background:

I have here a spreadsheet that I has a list of currency pairings on the left and I need to go find the correct rate/value in the tables with yellow headers on the right. The sheet will be formatted like this every time I export data however the Currencies pairings in B and C will constantly change, so I cannot simple reference the cell with it's corresponding value - I actually need to use VLOOKUP's and HLOOKUP's

The Goal:

Ultimate goal is to get the corresponding value/rate in column D

(1) Step one would be to use the Base Currency value in Column A, to identify what table on the right (Col I to AD) we should be looking at for the correct values
- this may require an HLOOKUP
(2) Using the string in column B, i.e. EURCAD - to look within the table we just identified above and select the right pairing.
- this may require a VLOOKUP


Specific Example:

So let's take an example. D3 would be GBPCAD which from the right tables with yellow headers gives you 1.63155. The forumula would ideally, would search the base currency (col A) among all the tables headers in yellow to the right, once it identifies it is the last table where it says GBP [AC:AD], it would than take the VLOOKUP Name [col C] 'GBpCAD' and search for this string within the table vertically, next it would output the value to the right.


This is definitely not impossible but somewhat challenging for myself alone, I would appreciate any help I can get - even a suggestion if you can't crack the formula. Anything to steer me in the right path is much appreciated

Assuming all tables are the same dimensions, try this in D3 and filled down

=VLOOKUP(C3,OFFSET($I$4:$J$13,0,MATCH(A3,$I$3:$AD$3,0)-1),2,0)

Originally Posted by Jonmo1

Assuming all tables are the same dimensions, try this in D3 and filled down

=VLOOKUP(C3,OFFSET($I$4:$J$13,0,MATCH(A3,$I$3:$AD$3,0)-1),2,0)

This works like a charm. You sir are the man, thank you so much!!!!! :D

You're welcome.