# vlookup returns some correct values, some #n/a and some incorrect values

#### jacehend

##### New Member
I'm doing a simple vlookup between two tabs in the same spreadsheet. Many returned values have #n/a but when I search the second tab they appear in the table. Some of the values are correct and some even return an incorrect value. Please help. I've had to manually lookup over 100 values in some cases because I can't find a solution.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### DataBlake

##### Well-known Member
I'm doing a simple vlookup between two tabs in the same spreadsheet. Many returned values have #n/a but when I search the second tab they appear in the table. Some of the values are correct and some even return an incorrect value. Please help. I've had to manually lookup over 100 values in some cases because I can't find a solution.
can you make a table of a couple examples and post your formula?
it's hard to identify whats wrong otherwise.

#### XOR LX

##### Well-known Member
Perhaps you're using TRUE (or, equivalently, 1) as VLOOKUP's 4th parameter, without quite understanding what that means?

Regards

#### jacehend

##### New Member
 My formula is =VLOOKUP(D282,'charge units'!A281:B1064,2,FALSE) "charge units" is the name of the tab containing the table. 461760 REPAIR OF NAIL BED 11760 450 659 #N/A 461762 RECNSTRCTN NAIL BED W GRAFT 11762 450 4220 #N/A 461765 WEDGE EXCISE SKIN NAIL FOLD 11765 450 438 #N/A 462001 REP SMP SCPNCKAXGNTRNKXTRM 2.5CM< 12001 450 295 15 462002 REP SMP SCPNCKAXGNTRNK XTRM 2.6 12002 450 295 17 462004 REP SMP SCPNCKAXGNTRNKXTRM 7.6CM 12004 450 295 1 462005 REP SMP SCPNCKAXGNTRNKXTRM 12.6CM 12005 450 438 #N/A 462006 REP SMP SCPNCKAXGNTRNKXTRM 20.1CM 12006 450 659 #N/A 462007 REP SMP SCPNCKAXGNTRNKXTRM OVER 3 12007 450 438 #N/A

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

#### jacehend

##### New Member
I didn't have my \$ signs above but I added them to the formula. That filled in many of the #n/a. I'm pulling two different Crystal Reports from our system. I don't know if the system is using different formats in the sheets and if that might be causing the problem.

#### DataBlake

##### Well-known Member
My formula is =VLOOKUP(D282,'charge units'!A281:B1064,2,FALSE)
"charge units" is the name of the tab containing the table.
could it be that you're looking up D282 in a range of A281:B1064?

try doing

\$1:\$1048576
Code:
``=VLOOKUP(D282,'charge units'!\$1:\$1048576,2,FALSE)``
and let me know if that works

that should find the value of D in sheet "charge units" and return the second column of its a match

#### jacehend

##### New Member
The \$ signs seem to have fixed it in this instance. I have to do a couple of reports every year that have the issue of randomly not reporting a result. Do you think it might have something to do with some sort of formatting issue from the reports being different? It just doesn't make sense because the formula is correct and it does pick up most of them.

#### DataBlake

##### Well-known Member
I have to do a couple of reports every year that have the issue of randomly not reporting a result. Do you think it might have something to do with some sort of formatting issue from the reports being different?
as long as the value being looked up are the same it shouldn't matter.
I.E if you have a date formatted as a percentage then it will still look up

the only time you receive an error generally is when your vlookup fails to find the value (D282) in the reference ('charge units'!\$1:\$1048576)
you get #N/A when the lookup fails
you get #Ref ! when the lookup doesn't have a valid reference (i.e not putting the \$ on your reference)

otherwise it should work?
p.s check for spaces in the number you are looking up before or after the number so " 35632" will not look up/match with "35642"

Last edited:

1,102,274
Messages
5,485,785
Members
407,515
Latest member
franjey

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...