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

[jacehend]

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.

 

[DataBlake]

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]

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

Regards

 

[jacehend]

[TABLE="width: 898"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]My formula is =VLOOKUP(D282,'charge units'!A281:B1064,2,FALSE)
"charge units" is the name of the tab containing the table.





461760 [/TD]
[TD="colspan: 2"]REPAIR OF NAIL BED[/TD]
[TD]11760 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]659[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]461762 [/TD]
[TD="colspan: 2"]RECNSTRCTN NAIL BED W GRAFT[/TD]
[TD]11762 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]4220[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]461765 [/TD]
[TD="colspan: 2"]WEDGE EXCISE SKIN NAIL FOLD[/TD]
[TD]11765 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]438[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]462001 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNKXTRM 2.5CM<[/TD]
[TD]12001 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]462002 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNK XTRM 2.6[/TD]
[TD]12002 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]462004 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNKXTRM 7.6CM[/TD]
[TD]12004 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]462005 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNKXTRM 12.6CM[/TD]
[TD]12005 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]438[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]462006 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNKXTRM 20.1CM[/TD]
[TD]12006 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]659[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]462007 [/TD]
[TD="colspan: 2"]REP SMP SCPNCKAXGNTRNKXTRM OVER 3[/TD]
[TD]12007 [/TD]
[TD] [/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]438[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[jacehend]

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]

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

=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]

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]

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"