Index and Match with Listed Items???

[Guest]

I am trying to offer a list of countries my buyers can get quotes from, then use the resulting country called out to pull an exchange rate from a table. I think the Index and Match function will work well, but I keep getting #N/A errors on the following formula

=INDEX('Exchange Rates'!A2:B8,MATCH(C2,'Exchange Rates'!A2:B8,0),2

Exchange Rates is the tab I have the rates table on, (first column is Countries, second is exchange rate), and C2 is the cell in the input tab that has the list of possible countries to obtain quotes from. What I want to see as a result of the formula is the exchange rate automatically filling itself out, depending on what country is pulled from the list. Please give me a pointer or two..

Jeff

 

[Frank Kabel]

Hi
try
=INDEX('Exchange Rates'!A2:B8,MATCH(C2,'Exchange Rates'!A2:A8,0),2)

--
Regards
Frank Kabel
Frankfurt, Germany

I am trying to offer a list of countries my buyers can get quotes

from, then use the resulting country called out to pull an exchange
rate from a table. I think the Index and Match function will work well,
but I keep getting #N/A errors on the following formula:

=INDEX('Exchange Rates'!A2:B8,MATCH(C2,'Exchange Rates'!A2:B8,0),2)

Exchange Rates is the tab I have the rates table on, (first column is

Countries, second is exchange rate), and C2 is the cell in the input
tab that has the list of possible countries to obtain quotes from. What
I want to see as a result of the formula is the exchange rate
automatically filling itself out, depending on what country is pulled
from the list. Please give me a pointer or two...

 

[Peo Sjoblom]

You have to use a one dimensional range for match, so you would need to use

MATCH(C2,'Exchange Rates'!A2:A8,0)

or

INDEX('Exchange Rates'!B2:B8, MATCH(C2,'Exchange Rates'!A2:A8,0))



Why don't you use VLOOKUP instead if you want the result from column B

VLOOKUP(C2,'Exchange Rates'!A2:B8,2,0)



--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

I am trying to offer a list of countries my buyers can get quotes from,

then use the resulting country called out to pull an exchange rate from a
table. I think the Index and Match function will work well, but I keep
getting #N/A errors on the following formula:

=INDEX('Exchange Rates'!A2:B8,MATCH(C2,'Exchange Rates'!A2:B8,0),2)

Exchange Rates is the tab I have the rates table on, (first column is

Countries, second is exchange rate), and C2 is the cell in the input tab
that has the list of possible countries to obtain quotes from. What I want
to see as a result of the formula is the exchange rate automatically filling
itself out, depending on what country is pulled from the list. Please give
me a pointer or two...

 

[Guest]

Thanks, Frank and Peo!