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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Lookup returning wrong value
- Thread starter BigJohn
- Start date
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.
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.
Upvote
0
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(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
Upvote
0
Gates Is Antichrist
Well-known Member
- Joined
- Aug 15, 2002
- Messages
- 1,961
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
) ]
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
) ]
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
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))
Upvote
0
Brian from Maui
MrExcel MVP
- Joined
- Feb 16, 2002
- Messages
- 8,459
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.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
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.
Upvote
0
Similar threads
- Question
