vlookup value checking specific criteria from multiple columns

Maybe, put this on K2 and ENTERED as array formulas, then copied down:

=INDEX($B$2:$B$170,MATCH(MAX(IF(($A$2:$A$170=J2)*($F$2:$F$170="VC"),$G$2:$G$170)),$G$2:$G$170,0))

Originally Posted by azumi

Maybe, put this on K2 and ENTERED as array formulas, then copied down:

=INDEX($B$2:$B$170,MATCH(MAX(IF(($A$2:$A$170=J2)*($F$2:$F$170="VC"),$G$2:$G$170)),$G$2:$G$170,0))

i am getting an error with this, its not picking up the right values?

Originally Posted by kvsrinivasamurthy

In K2 then copy down

=IFERROR(INDEX(Table1[ITEM],AGGREGATE(15,6,ROW(Table1[CODE])/(((Table1[CODE]=$J2)*(Table1[STATUS]="VC")*Table1[[On hand ]])=(1/(1/(MAX((Table1[CODE]=$J2)*(Table1[STATUS]="VC")*Table1[[On hand ]]))))),1)-ROW($A$2)+1),"")

Nice Thanks This one works good as it picks up the 'VC' value and leaves blank if not available . However Can it pickup the next highest value on hand if the "VC" is not available or zero value?