INDEX and MATCH Combo Formula not Working across Spreadsheets

[Luincale]

Hi all,

I've had trouble getting this formula to work and just know it must be something simple I am missing. Please add a pair of fresh eyes for me! Here's the formula I start with:

=INDEX(Table3,MATCH(K3,Table3[[#All],[State]],0),(MATCH(L3,Table3[#Headers],0)))

This runs smoothly; however, when I attempt to move the formul to another spreadsheet within the same workbook and change the references to K3 and L3, it stops working and gives me a #N/A error.

=INDEX(Table3,MATCH(E3,Table3[[#All],[State]],0),(MATCH(D5,Table3[#Headers],0)))

E3 in the above formula contains a VLOOKUP, but when I enter something directly, I still have issues, so I am assuming the actual problem is regarding the spreadsheet change. THe same happens if I attempt to link the "K3" and L3" cell references in the first formula to another spreadheet in the workbook, too. What am I wrongly assuming?

 

[wideboydixon]

The #N/A suggests that one of the MATCH functions isn't returning a value. Try selecting the cell with the formula and then using "Evaluate Formula" on the Formulas tab to step through the evaluation of the formula and see where the problem lies.

WBD