INDEX MATCH not pulling in product number

System

This discussion was created from comments split from: Does INDEX(MATCH) work on a column with a formula?.

Samantha S.

I can't seem to get this to work with mine.

On Sheet 1, I have Product # being manually entered and need to pull its product name from Sheet 2:

On Sheet 2, I imported an Excel file which converted all my Product #s to include an apostrophe at the beginning. I had to then create a second Product # column using a formula to extract just the value:

Now I need Sheet 1 to Match the Product Numbers but keep getting a No Match error, even when adding VALUE in. What am I missing?

Nicole Pratt

Following in hopes for a response to @Samantha S. I'm running across this same issue

TVang

Hi, @Samantha S. , change your "Product Number2" column formula to:

IFERROR(VALUE([Product number]@row), [Product number]@row)

As it is, all of the values in the "Product Number2" column (Sheet 2) are text, while those in the "Product #" column (Sheet 1) are numbers. The two will not match.

RIGHT() is a text function and will return a string (your original formula). VALUE() converts what looks like numbers into a numeric value.

In the formula above, VALUE() will fail if [Product number]@row contains non-numeric characters. When that happens, IFERROR() returns the original value.

Hope this helped!