Using Index Match across more than one WorkSheet get - #N/A error

[Knightflier]

Any suggestions would be appreciated.

[Knightflier]

Do not think this got attached the first time, sorry about that.

Hello Excel Forum:
I am using INDEX MATCH to pull an equipment description from an Excel Table and transfer to another Sheet (same workbook). I can get the Index Match to work all on the same worksheet, but for some reason, I get a #N/A when I attempt to use the function across different worksheets. I am using range names and Table names as well. I must be overlooking something very basic, never the less it’s giving me a headache.

Knightflier

[FlameRetired]

Your tbl_Equip1[Unit '#] data are text "numbers" that have a numberic value of 0. Your lookup values in 'Eq_Quartelyreport' are actual numbers. The data type must match.

You can coerce those text "numbers" into their numeric values by math operations such as adding 0 or multiplying by 1. Like this

Please Login or Register  to view this content.

It works at my end.

[Knightflier]

Hello FlameRetired:
I should have labeled the "Unit# as "Unit Label" as this is an accounting code that may be alphanumeric, see row 30 0n Eq_QuartrlyReport, "E106-4543"
Thanks for responding

[FlameRetired]

OK.
Then try coercing the lookup values to text by concatenating "" like this.

Please Login or Register  to view this content.