Help on using Index, Match and Vlookup

[Ron]

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!!!!

 

[JBeaucaire]

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))

 

[Max]

One way is to use a multi-criteria index/match, indicatively:
=index(ReturnCol,match(1,index((Cond1)*(Cond2)*(Cond3),),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