| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
| table lookup | Excel Discussion (Misc queries) | |||
| Lookup table help please | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| lookup or table, not sure what to do | Excel Worksheet Functions | |||
Linear Mode
