Using INDEX MATCH as VLookup - Multiple Worksheets

[crayroge]

Hi,

I am trying to use the INDEX MATCH formula to get these state codes into this field.

Sheet 1 has the address (Column A) , city (Column B), state(Column C), stateNAME (Column C), stateCode (Column D)

address city stateName stateCode
PO Box 1 Austin Texas
PO Box 2 Dallas Texas
PO Box 3 FT Worth Texas
PO Box 4 Galveston Texas
PO Box 5 Houston Texas
PO Box 6 Greenvile Texas
PO Box 7 Atlanta Georgia
PO Box 8 New OrleansLousiana
PO Box 9 New York New York

Sheet 2 has the stateName (Column A), stateCode (Column B)

stateName stateCode
Texas TX
Georgia GA
Lousiana LA
New York NY

I used this function below and its giving me the first answer for the stateCode but N/A'ing the other states....what could I be missing?

=INDEX(Sheet2!$B$2:$B$5,MATCH(Sheet1!$C$2:$C$10,Sheet2!A2:A5,0))

 

[Fluff]

How about
=INDEX(Sheet2!$B$2:$B$5,MATCH(C2,Sheet2!A$2:A$5,0))

 

[crayroge]

thanks! This worked beautifully!

 

[Fluff]

You're welcome & thanks for the feedback