Index Match Repeatedly Returns #N/A

PipBoy808

Board Regular
Joined
Oct 30, 2013
Messages
107
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 :)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Could you post some sample data and the formula you are using
 
Upvote 0
What does

=isnumber(D1)

return? Replace D1 with an offending match
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top