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

jacehend

New Member
Joined
Apr 30, 2019
Messages
4
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.
 

Some videos you may like

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
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
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
Joined
Jul 2, 2012
Messages
4,517
Perhaps you're using TRUE (or, equivalently, 1) as VLOOKUP's 4th parameter, without quite understanding what that means?

Regards
 

jacehend

New Member
Joined
Apr 30, 2019
Messages
4
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 BED11760 450659#N/A
461762 RECNSTRCTN NAIL BED W GRAFT11762 4504220#N/A
461765 WEDGE EXCISE SKIN NAIL FOLD11765 450438#N/A
462001 REP SMP SCPNCKAXGNTRNKXTRM 2.5CM<12001 45029515
462002 REP SMP SCPNCKAXGNTRNK XTRM 2.612002 45029517
462004 REP SMP SCPNCKAXGNTRNKXTRM 7.6CM12004 4502951
462005 REP SMP SCPNCKAXGNTRNKXTRM 12.6CM12005 450438#N/A
462006 REP SMP SCPNCKAXGNTRNKXTRM 20.1CM12006 450659#N/A
462007 REP SMP SCPNCKAXGNTRNKXTRM OVER 312007 450438#N/A

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

jacehend

New Member
Joined
Apr 30, 2019
Messages
4
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
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
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
Joined
Apr 30, 2019
Messages
4
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
Joined
Jan 26, 2015
Messages
779
Office Version
2016
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top