Index and Match with Listed Items???

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Hi
try
=INDEX('Exchange Rates'!A2:B8,MATCH(C2,'Exchange Rates'!A2:A8,0),2)

--
Regards
Frank Kabel
Frankfurt, Germany

jmdaniel said:
I am trying to offer a list of countries my buyers can get quotes
Click to expand...
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
Click to expand...
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...
 
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

jmdaniel said:
I am trying to offer a list of countries my buyers can get quotes from,
Click to expand...
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
Click to expand...
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...
 
Back
Top