highest "priority" among all matched instances (multiple conditions)

(Pardon my poor English.)

I have raised a similar question before. (Please check out: http://www.excelforum.com/excel-form...instances.html)

In the previous case, there is only 1 determining condition. Now if I have multiple conditions, how could I get the results?

e.g.

untitled.PNG

Column A to C are the raw data
Column G is the priority list
Column K display the result I want
K should display the value from column B with the highest priority, matching the values of I and J with those from A and C respectively.

For example,
I1 = M, J1 = B
we go through the raw data and find 2 matching pairs
(B, S322, M) and (B, G201, M)
since S322 has a higher priority over G201, K1 would display S322

What should be the formula in cells in column K in order to get the desired results?
Thanks in advance.

=INDEX($G$1:$G$5,SMALL(IFERROR(MATCH(IF((I1=$C$1:$C$6)*(J1=$A$1:$A$6),$B$1:$B$6,""),$G$1:$G$5,0),""),1)) cse

Originally Posted by eisayew

=INDEX($G$1:$G$5,SMALL(IFERROR(MATCH(IF((I1=$C$1:$C$6)*(J1=$A$1:$A$6),$B$1:$B$6,""),$G$1:$G$5,0),""),1)) cse

thanks again!

Originally Posted by benishiryo

hi dyuk, would you mind uploading a sample Excel file next time instead of a picture. otherwise, we have to re-create the picture manually in Excel. here's what you can use:

Please Login or Register  to view this content.

paste inside the formula bar & press CTRL + SHIFT + ENTER

thanks for your advice, i would upload a file instead next time