Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup more than one criterion
Hi,
Am trying to look up prices that I quoted a client recently to ensure that I quote the same price. The problem is that the same part number may be manufactured by more than one company, so I have to quote on those, not just one. Vlookup only finds the first one... Example simplified: Part no. Mfg Price A-100 ABC 2.00 A-100 XYZ 3.00 A-100 LMN 1.50 L-987 XYZ 0.75 L-987 MNO 0.70 L-987 ABC 0.71 Any ideas?? Thanks -- Thanks! Dee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup more than one criterion
Assuming that A2:C7 contains the data, try the following formula which
needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(C2:C7,MATCH(1,IF(A2:A7="A-100",IF(B2:B7="XYZ",1)),0)) Hope this helps! In article , dee wrote: Hi, Am trying to look up prices that I quoted a client recently to ensure that I quote the same price. The problem is that the same part number may be manufactured by more than one company, so I have to quote on those, not just one. Vlookup only finds the first one... Example simplified: Part no. Mfg Price A-100 ABC 2.00 A-100 XYZ 3.00 A-100 LMN 1.50 L-987 XYZ 0.75 L-987 MNO 0.70 L-987 ABC 0.71 Any ideas?? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup more than one criterion
Thank you. I modified it slightly so that it referred to cell addresses as
opposed to specific content. I will also add something so that if a match isn't found, I won't have the #n/a problem. -- Thanks! Dee "Domenic" wrote: Assuming that A2:C7 contains the data, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX(C2:C7,MATCH(1,IF(A2:A7="A-100",IF(B2:B7="XYZ",1)),0)) Hope this helps! In article , dee wrote: Hi, Am trying to look up prices that I quoted a client recently to ensure that I quote the same price. The problem is that the same part number may be manufactured by more than one company, so I have to quote on those, not just one. Vlookup only finds the first one... Example simplified: Part no. Mfg Price A-100 ABC 2.00 A-100 XYZ 3.00 A-100 LMN 1.50 L-987 XYZ 0.75 L-987 MNO 0.70 L-987 ABC 0.71 Any ideas?? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and DGET to find a value with multiple criterion | Excel Worksheet Functions | |||
Find Multiple instances of Single Criterion in Row & Return To a Single Col | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |