Lookup returning wrong value

BigJohn

New Member
Joined
May 24, 2006
Messages
19
I haven't posted many times in this forum because I've always found the solution to my problems wothout having to. Thanks to all and keep up the good work. Here's my newest problem: I have a lookup function returning the wrong data. I am looking up strings of text that may or may not exist in a data import. If the string does not exist, the lookup seems to choose the string that is close in makeup to the lookup. Is there any way to return a "0" when the string doesn't exist? Thanks in advance for any help.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ff_emt1

Board Regular
Joined
Jan 7, 2005
Messages
63
Try this

I use the following formula to look up part numbers in an "item master" table. If I enter a part number in cell A18 that is not in the table, this cell stays blank.

=IF(ISERROR(VLOOKUP(A18,'Item Master'!$A$3:$C$10802,3, FALSE)),"",(VLOOKUP(A18,'Item Master'!$A$3:$C$10802,3,FALSE)))

Just put 0 in between the 2 quotes and it will put 0 if there is nothing matching.

Maybe this will help.
 

BigJohn

New Member
Joined
May 24, 2006
Messages
19
I've pasted the actual formula and table data below. In this particular instance I'm looking up a manufactured part number and need to return the daily counted inventory. If there is no inventory counted it will not be listed. I am looking up the part number contained in my spreadsheet and trying to return inventory values from imported data. The part number I'm looking up is 56a601 - xxx. When I lookup 56a601 - 43, the formula returns the value for 56A601 - 330 (60). Thanks again.




=LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86)

CP580 | 56A601 - COUNT POINT 3 | 56A601 - 123 | 8 | 39007 COUNT POINT
CP581 | 56A601 - COUNT POINT 4 | 56A601 - 205 | 25 | 39007 COUNT POINT
CP582 | 56A601 - COUNT POINT 5 | 56A601 - 275 | 19 | 39007 COUNT POINT
CP584 | 56A601 - COUNT POINT 7 | 56A601 - 330 | 60 | 39007 COUNT POINT
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961

ADVERTISEMENT

The key here is that by default (when omitted) the 4th argument is TRUE. You want to specify FALSE here.

That TRUE case is quite vexing - I've even argued (and still disagree) with guru Chip about it. It's maddening that it's the default, since its behavior is practically a matter of conjecture in some cases. (I.e., consider TRUE to be undocumented for non-sorted lookup ranges, in current Excel versions.)

[ Edit: all of the above concerns the use of VLOOKUP and HLOOKUP, which seem to be what people mean most of the time when they say lookup. Of course, go with Aladin's otherwise. (In fact, always go with Aladin's solutions. No exceptions :) ) ]
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I've pasted the actual formula and table data below. In this particular instance I'm looking up a manufactured part number and need to return the daily counted inventory. If there is no inventory counted it will not be listed. I am looking up the part number contained in my spreadsheet and trying to return inventory values from imported data. The part number I'm looking up is 56a601 - xxx. When I lookup 56a601 - 43, the formula returns the value for 56A601 - 330 (60). Thanks again.




=LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86)

CP580 | 56A601 - COUNT POINT 3 | 56A601 - 123 | 8 | 39007 COUNT POINT
CP581 | 56A601 - COUNT POINT 4 | 56A601 - 205 | 25 | 39007 COUNT POINT
CP582 | 56A601 - COUNT POINT 5 | 56A601 - 275 | 19 | 39007 COUNT POINT
CP584 | 56A601 - COUNT POINT 7 | 56A601 - 330 | 60 | 39007 COUNT POINT

If the table area is sorted in ascending order on column C (which is a good idea), try:

=IF(LOOKUP(C10,'Intranet - Import'!$C$3:$C$86)=C10,LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86),"Not Found")

If the sorting condition cannot be upheld, try:

=INDEX('Intranet - Import'!$E$3:$E$86.MATCH(C10,'Intranet - Import'!$C$3:$C$86,0))
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

I've pasted the actual formula and table data below. In this particular instance I'm looking up a manufactured part number and need to return the daily counted inventory. If there is no inventory counted it will not be listed. I am looking up the part number contained in my spreadsheet and trying to return inventory values from imported data. The part number I'm looking up is 56a601 - xxx. When I lookup 56a601 - 43, the formula returns the value for 56A601 - 330 (60). Thanks again.




=LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86)

CP580 | 56A601 - COUNT POINT 3 | 56A601 - 123 | 8 | 39007 COUNT POINT
CP581 | 56A601 - COUNT POINT 4 | 56A601 - 205 | 25 | 39007 COUNT POINT
CP582 | 56A601 - COUNT POINT 5 | 56A601 - 275 | 19 | 39007 COUNT POINT
CP584 | 56A601 - COUNT POINT 7 | 56A601 - 330 | 60 | 39007 COUNT POINT

LOOKUP requires your lookup column to be sorted, which it seems to be. There maybe leading/trailing spaces in the lookup column. You may also want to consider switching to VLOOKUP which has a 4th argument to find the exact match and the lookup column need not be sorted.
 

BigJohn

New Member
Joined
May 24, 2006
Messages
19
I've pasted the actual formula and table data below. In this particular instance I'm looking up a manufactured part number and need to return the daily counted inventory. If there is no inventory counted it will not be listed. I am looking up the part number contained in my spreadsheet and trying to return inventory values from imported data. The part number I'm looking up is 56a601 - xxx. When I lookup 56a601 - 43, the formula returns the value for 56A601 - 330 (60). Thanks again.




=LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86)

CP580 | 56A601 - COUNT POINT 3 | 56A601 - 123 | 8 | 39007 COUNT POINT
CP581 | 56A601 - COUNT POINT 4 | 56A601 - 205 | 25 | 39007 COUNT POINT
CP582 | 56A601 - COUNT POINT 5 | 56A601 - 275 | 19 | 39007 COUNT POINT
CP584 | 56A601 - COUNT POINT 7 | 56A601 - 330 | 60 | 39007 COUNT POINT

If the table area is sorted in ascending order on column C (which is a good idea), try:

=IF(LOOKUP(C10,'Intranet - Import'!$C$3:$C$86)=C10,LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86),"Not Found")

If the sorting condition cannot be upheld, try:

=INDEX('Intranet - Import'!$E$3:$E$86.MATCH(C10,'Intranet - Import'!$C$3:$C$86,0))


Aladin, that worked. I can't figure out why, but it worked. Can you explain why the if statement returned "not found"? Thanks.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I've pasted the actual formula and table data below. In this particular instance I'm looking up a manufactured part number and need to return the daily counted inventory. If there is no inventory counted it will not be listed. I am looking up the part number contained in my spreadsheet and trying to return inventory values from imported data. The part number I'm looking up is 56a601 - xxx. When I lookup 56a601 - 43, the formula returns the value for 56A601 - 330 (60). Thanks again.




=LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86)

CP580 | 56A601 - COUNT POINT 3 | 56A601 - 123 | 8 | 39007 COUNT POINT
CP581 | 56A601 - COUNT POINT 4 | 56A601 - 205 | 25 | 39007 COUNT POINT
CP582 | 56A601 - COUNT POINT 5 | 56A601 - 275 | 19 | 39007 COUNT POINT
CP584 | 56A601 - COUNT POINT 7 | 56A601 - 330 | 60 | 39007 COUNT POINT

If the table area is sorted in ascending order on column C (which is a good idea), try:

=IF(LOOKUP(C10,'Intranet - Import'!$C$3:$C$86)=C10,LOOKUP(C10,'Intranet - Import'!$C$3:$C$86,'Intranet - Import'!$E$3:$E$86),"Not Found")

If the sorting condition cannot be upheld, try:

=INDEX('Intranet - Import'!$E$3:$E$86.MATCH(C10,'Intranet - Import'!$C$3:$C$86,0))


Aladin, that worked. I can't figure out why, but it worked. Can you explain why the if statement returned "not found"? Thanks.

The LOOKUP idiom above behaves like the Index/Match idiom that does the exact match (for the match-type in MATCH is set to 0). But it does all this much faster because the sort order.

The LOOKUP formula first checks whether the lookup value is in the match range (i.e., C3:C86). If that's the case, the first lookup value returns a value which is exactly the same as the lookup value (hence the equality test). The successfull equality test invokes the full LOOKUP which returns the desired value. Otherwise, that is, when the equality test fails, "not found" is returned.
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

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
Top