Index Match Repeatedly Returns #N/A

[PipBoy808]

I have two worksheets, both of which are formatted as numbers in their entirety. I'm using an index-match formula for a couple of hundred values in D:D of Sheet 1 to lookup values in B:B of a table in Sheet 2. However, it repeatedly returns an #N/A. I've tried reformatting everything, using structured references, unstructured references, but it's not working.

The really frustrating part is that if I step through the formula, it all seems to work almost perfectly. My structured references identify the correct range of cells. However, the MATCH element of the formula keeps returning an #N/A even though the values are there to be matched.

Does anyone know of any typical reason why this might occur?

Thanks in advance

 

[Momentman]

Could you post some sample data and the formula you are using

 

[steve the fish]

What does

=isnumber(D1)

return? Replace D1 with an offending match

 

[bschwartz]

Typically, this can happen with numbers when they are formatted as text in one place and as numbers in another, or have leading or trailing spaces in one of the two locations. Can you post an example of a value you are trying to match, that is returning #N/A? Post the value from each worksheet. You might also try a simple comparative operator on it, to see what the output is. For instance, if matching a cell D4 with Sheet2 cell B2, use the formula =D4=Sheet2!B2 . If Excel sees a match, it will return TRUE. If they don't match, it will output FALSE.

 

[PipBoy808]

The ISNUMBER suggestion has me closer to the answer. The data table returns ISNUMBER=FALSE for all values, even though the format option is selected as 'Number'. I suspect this may have something to do with the fact that it is pulling from a SQL database where I can't touch the data.

Performing ISTEXT on the data table values returns TRUE. So, it looks like I need to convert the values in in my destination sheet - Sheet 1 - to text for the lookup to work. This is easier said than done. Formatting the cells as 'text' still returns ISTEXT=FALSE in Sheet 1. Even if I type the number '1' into a cell in a random sheet, and convert the Number Value of the cell to text, ISTEXT still returns False.

Has anyone any advice in converting numbers to text?

 

[steve the fish]

Click the column. Data tab. Text to columns. Finish.

 

[PipBoy808]

Click the column. Data tab. Text to columns. Finish.

ISTEXT=FALSE

I can't even do this in a brand new workbook. ISTEXT just repeatedly returns False.

 

[steve the fish]

That's what you would expect now they are numbers

 

[PipBoy808]

That's what you would expect now they are numbers

Oh **** motha****a it worked!

Sorry I was thinking of things in reverse. Thanks for your help!