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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
Perhaps you're using TRUE (or, equivalently, 1) as VLOOKUP's 4th parameter, without quite understanding what that means?

Regards
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top