Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help on using Index, Match and Vlookup
I have 3 conditions MPn and Vendor code and Mfg code must be correct before
the vlookup function can search for the correct data. I tried to use the following =INDEX('Vendor part'!$F$16:$F$67,MATCH(D17,'Vendor part'!$A$16:$A$67,0)) it does work to a certain extend but when it comes to the same vendor part number with different Mfg code it use the first vendor description. Tried several other combinations but not successful. Can some one share your thoughts!!!! -- Thanks........ |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help on using Index, Match and Vlookup
You don't provide the background information about that other data, so I'll
make it up for the purpose of providing a suggestion. Sheet "Vendor Part' ....Column A = Vendor Codes ....Column B = MPn Codes ....Column C = Mfg Codes Current sheet ....D17 = Vendor code to match ....E17 = MPn code to match ....F17 = Mfg code to match Complex INDEX(MATCH(): =INDEX('Vendor part'!$F$16:$F$67, MATCH(D17 & E17 & F17, INDEX('Vendor part'!$A$16:$A$67 & 'Vendor part'!$B$16:$B$67 & 'Vendor part'!$C$16:$C$67, 0), 0)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Ron" wrote: I have 3 conditions MPn and Vendor code and Mfg code must be correct before the vlookup function can search for the correct data. I tried to use the following =INDEX('Vendor part'!$F$16:$F$67,MATCH(D17,'Vendor part'!$A$16:$A$67,0)) it does work to a certain extend but when it comes to the same vendor part number with different Mfg code it use the first vendor description. Tried several other combinations but not successful. Can some one share your thoughts!!!! -- Thanks........ |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help on using Index, Match and Vlookup
One way is to use a multi-criteria index/match, indicatively:
=index(ReturnCol,match(1,index((Cond1)*(Cond2)*(Co nd3),),0)) where Conditions 1 to 3 would refer to where the 3 criteria (ie your MPn, vendor code and mfg code) are simultaneously satisfied. The expression can be normally Entered Assuming 3 corresponding inputs: zzz, xxx & yyy for checking against the cols for MPn, Vendor Code, Mfg Code the 3 conditions would be indicatively Cond1: MPn_range = zzz Cond2: VendCode_range = xxx Cond3: MfgCode_range = yyy ReturnCol would be the result col that you want the result to be extracted/returned by the index/match Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Ron" wrote: I have 3 conditions MPn and Vendor code and Mfg code must be correct before the vlookup function can search for the correct data. I tried to use the following =INDEX('Vendor part'!$F$16:$F$67,MATCH(D17,'Vendor part'!$A$16:$A$67,0)) it does work to a certain extend but when it comes to the same vendor part number with different Mfg code it use the first vendor description. Tried several other combinations but not successful. Can some one share your thoughts!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup vs. Match/Index | Excel Worksheet Functions | |||
VLOOKUP, MATCH, INDEX HELP! | Excel Worksheet Functions | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
Vlookup, Index & Match | Excel Worksheet Functions |