Table lookup

[Guest]

I’ve a currency table with historical exchange rates for the year.
The months, Jan, Feb, Mar…. are column labels and the currencies
CZK, DKK, EUR, HUF…(sorted) are row labels and the different monthly
exchange rate in the intersections.
I’d like to find the rate for EUR in April but VLOOKUP or HLOOKUP
only gives me a REF error. It seems that it doesn’t accept other
that 1, 2 ,3, 4…. as column labels in Vlookup or as row labels in HLookup.
Strange, I think I’ve played with these two ones before and it worked OK.

 

[Guest]

Use INDEX/MATCH to give intersection of Month and Currency

=INDEX($B$2:$M$10,MATCH($J$2,$A$2:$A$10,0),MATCH($J$1,$B$1:$M$1,0))

j2=Currency Code
J1=Date
b2:M10 contain currency rates

HTH

 

[Mike Fogleman]

Tools/Options Calculations - Allow Labels in Formulas.

Mike F

 

[Guest]

Thanks Toppers,
it worked fine, even looking up in the currency table in an external file.
Cheers
Mats

 

[Guest]

Thank you for thefeedback.

Thanks Toppers,
it worked fine, even looking up in the currency table in an external file.
Cheers
Mats

 

[Guest]

Thanks Mike,
but it won't help! The option is already ticked.
It seems curious that a row or column indexes must have 1,2 ,3 or A, B, C
labels
to work. Excel is usually more intellegent!
But anyway, Toppers proposal solved it.