LOOKUP question
Posted by Casey on July 27, 2001 12:41 PM
i have a spreadsheet that has many numerous columns/rows. it is a price guide that shows our cost, customer price, etc. within that same file, i have another worksheet that lists all product numbers in column A, and in column B there are the product lines corresponding with the product numbers. in my price guide, i have a list of part numbers, but not all of the part numbers that are in the second worksheet. what i need is, in my price guide worksheet, i need it to search in the second worksheet for the part number and for my value i need it to equal the product line according to the part number. so, this is the formula i have in there now:
=LOOKUP(B47,'PRODUCT LINES'!$A$1:$A$12153,'PRODUCT LINES'!$B$1:$B$12153)
description:
"B" is the part number from the price guide
"PRODUCT LINES-COLUMN A"=is the list of part numbers that it needs to find a match for from column "B" in price guides
"PRODUCT LINES-COLUMM B"=is the actual product lines that i want it to reference in my price guides worksheet corresponding with the part number in column "B" from my price guides worksheet
HOWEVER, what it is doing is, if it can't find the exact matching part number, it will substitute the product line for the closest part number that it is searching for. i don't want it to do that. if it could put a value in there that is ZZZ when it CAN'T find the part number, that would be PERFECT. i don't know code, so if you could please let me know in formula format, i would appreciate it!!!
Posted by Aladin Akyurek on July 27, 2001 12:52 PM
Casey,
Use instead
=IF(ISNUMBER(MATCH(B47,'PRODUCT LINES'!$A$1:$A$12153,0)), VLOOKUP(B47,'PRODUCT LINES'!$A$1:$B$1:$B$12153,2,0),"ZZZ")
Note. You might want to name the range $A$1:$A$12153 LVALUES and the range $A$1:$B$1:$B$12153 (on PRODUCT LINES) LTABLE via the Name Box. And use these names in the above formula.
Aladin
