Excel vLookup #N/A error

Hi Shepherd. I am an Excel user like you.

First, what is the formula you are using? I would help to see the exact formula.

In the meantime, you may have already checked for these, but this is what I look for first.

Check for extra spaces particularly at the beginning or end of either the lookup value or the target line in the lookup table. They will result in a mismatch.

Check the range of your lookup table. Make sure that you have the correct rows and columns referenced and that it is in Absolute Value form with the $ signs so that the formula can be drug down.

Make sure that the Row reference for the Lookup Value in the formula matches the row that the formula is in. If it is off by a row or two it can create strange results.

I notice that you have the filter turned on for the sheet with the reference table. If something is filtered it may throw off the table range as you enter it into the formula making it easy to choose the wrong row, for example as the first or last rows of the table. Make sure that there are no filters on and then double check the table range.

Reply if you have additional questions or more information. Please mark this reply as answered if this solves your question.

Rich~M

Hi,

You did not follow my advice. As requested in my previous message, i mentioned that you should copy the lookup value from one sheet to another to check what happens. When i copied A2 from the lookup table worksheet to cell A2 of the Open Time vLookup formula table worksheet, the VLOOKUP() returns the correct answer. So my surmise was correct.

Now that we know what the problem is, write this formula in cell B2 of the Open Time vLookup formula table worksheet

=VLOOKUP(A2,SUBSTITUTE(Table1,CHAR(160),CHAR(32)),2,FALSE)

Hope this helps.

It would help us help you if you uploaded an example Excel file to a file-sharing website, and posted the download URL is a response here. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.

It appears that the expression is VLOOKUP(B5, A2:..., 8, FALSE), and you expect B5=A2 to be true. In fact, is it?

That is, for whatever cell in column A that you expect to match (A2, for example), what does =B5=A2 return?

In the image, the strings appear to be the same. In particular, there does not appear to be any extraneous spaces.

But looks can be deceiving. And not all spaces are the same. There are normal spaces (ASCII 32) and non-breaking spaces (ASCII 160). Also, a tab (ASCII 9) can look like a single space. Finally, there could be not non-displayable control characters.

One way to see these differences is to enter the following into C5 (for example) and drag across (into D5, E5, etc) for at least LEN(B5) columns.

=IF(COLUMNS($C5:C5)>LEN($B5), "", CODE(MID($B5,COLUMNS($C5:C5),1)))

Do the same for A2 starting in C6, to wit:

=IF(COLUMNS($C6:C6)>LEN($A2), "", CODE(MID($A2,COLUMNS($C6:C6),1)))

PS.... Also check for other culprits that cause "inexplicable" errors, for example:

1. Is Automatic Calculation enabled?
2. Do you have any circular references, even in unrelated cells (and worksheets?)? Click Format > Error Checking > Circular References. The notification on the lower lefthand status bar might not always be correct.