VLookUp- Help Please!!

**vgilbert** · 12-13-2011, 10:57 PM

I am working with Vlookup and I will attempt to explain the error I am getting. I am working with two datasheets.

I have a list of about 687 records in data sheet 1 and about 385 records in datasheet 2. I am using the same a unique Id in both data sheet to match records. When I perform the vlookup in datasheet 1 against datasheet 2, and copy it down the column, it returns a value for the first few records , but only to the point in which a record is found. Instead of returning a #NA value only for the record that is not found, it returns a value of #NA for the rest of the records, of which I know there are other matching unique ID’s in datasheet 2. If I remove the record that is not found, and copy the formula again, it will again return values, but only to the next records that is not found… I hope I am explaining the error properly. The unique ID columns are formatted the same.

I am befuddled. Can you help?

Thanks…

**abousetta** · 12-13-2011, 11:26 PM

Hello and welcome to the forum,

Without seeing your formula I think that you have forgotten to put the magical dollar signs around your table references. For example:

=VLOOKUP(A1,B1:D10,0)

dragged down (10 rows later) will result in:

=VLOOKUP(A11,B11:D20,0)

see the problem. The table is moving down.

Now it should look like:

=VLOOKUP(A1,$B$1:$D$10,0)

Hope this helps.

abousetta

**Ishtiyaq** · 12-13-2011, 11:37 PM

Hi,

If there is any difference in your unique IDs then vlookup will not work. By difference what I mean is if your unique ID in main database contains an additional space after the digit but in the sheet where you are using vlookup that space is not present for that value, vlookup will not work. Press F2 and delete additional space or add space to check values for which vlookup is not working, but you know for sure corresponding value exist in your database.

Although I can't say that's definitely the problem in your case, as there could some other issue as well. It would be better if you can attach that excel sheet, so we can have a look at it.

Warm regards
Ishtiyaq

**abousetta** · 12-13-2011, 11:50 PM

A simple way to test for slight (invisible) irregularities is to use a simple equation. If we say that the contents of B25 in two sheet 1 and sheet 2 should be the same then in another cell you can put this formula: