Table lookup
 

Ive 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.
Id like to find the rate for EUR in April but VLOOKUP or HLOOKUP
only gives me a REF error. It seems that it doesnt accept other
that 1, 2 ,3, 4€¦. as column labels in Vlookup or as row labels in HLookup.
Strange, I think Ive played with these two ones before and it worked OK.

Table lookup
 

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

"Mats Samson" wrote:

Ive 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.
Id like to find the rate for EUR in April but VLOOKUP or HLOOKUP
only gives me a REF error. It seems that it doesnt accept other
that 1, 2 ,3, 4€¦. as column labels in Vlookup or as row labels in HLookup.
Strange, I think Ive played with these two ones before and it worked OK.

Table lookup
 

Tools/Options Calculations - Allow Labels in Formulas.

Mike F
"Toppers" wrote in message
...
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

"Mats Samson" wrote:

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.

Table lookup
 

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

"Toppers" wrote:

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

"Mats Samson" wrote:

Ive 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.
Id like to find the rate for EUR in April but VLOOKUP or HLOOKUP
only gives me a REF error. It seems that it doesnt accept other
that 1, 2 ,3, 4€¦. as column labels in Vlookup or as row labels in HLookup.
Strange, I think Ive played with these two ones before and it worked OK.

Table lookup
 

Thank you for thefeedback.

"Mats Samson" wrote:

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

"Toppers" wrote:

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

"Mats Samson" wrote:

Ive 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.
Id like to find the rate for EUR in April but VLOOKUP or HLOOKUP
only gives me a REF error. It seems that it doesnt accept other
that 1, 2 ,3, 4€¦. as column labels in Vlookup or as row labels in HLookup.
Strange, I think Ive played with these two ones before and it worked OK.

Table lookup
 

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.

"Mike Fogleman" wrote:

Tools/Options Calculations - Allow Labels in Formulas.

Mike F
"Toppers" wrote in message
...
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

"Mats Samson" wrote:

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.