VLOOKUP formula not working

**maryren** · 08-08-2013, 04:35 PM

My formula is: =VLOOKUP($A2,Sheet1!A:D,4,0) and the message returned in the cell is #N/A (Value not available error).

I've used this formula in other worksheets and it has worked fine. The lookup field is a number that can also have a - or letter assigned to it (i.e. 1010-B). I tried changing the numbers to text to see if that would help, but it did not.

I am not sure how to solve and am not sure what I am missing. Any guidance is appreciated.

**popipipo** · 08-08-2013, 04:39 PM

Sometimes a space at the end is the problem.
For better help we need the excelfile.

**FDibbins** · 08-08-2013, 05:21 PM

Hi and welcome to the forum

That error message normally means it cannot find what you want it to look for. If you know that the value you are looking for is really there, do a =exact(cell1,cell2) to see if they really are the same.

Check for leading and trailing spaces as well

When all else fails, upload a sample workbook

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**martindwilson** · 08-08-2013, 05:26 PM

=exact(cell1,cell2) is not a good indicator as it's case sensitive

Formula:

Please Login or Register  to view this content.

a simple =a1=b1 is a better test
also exact tests text strings so a real number 100 compared with a text number 100 comes out as true ! that surprised me but it does but if you use
=text number=real number you get false