Priority Based Lookup

Hi,

Just wondering if someone can help me with the attached file.

What i have on the sheet are 3 removal dates with 3 removal reasons. I need to bring across the date and reason based on the priority table on my right of the sheet.

for example the first row has a 1st removal reason of Blue drive, second removal reason of purple drive and third removal reason of orange drive. Based on the priority table i need the first reason to have a priority of "1" to show itself in the highlighted row along with the date.

Hope this makes sense.

Thank you

Viren

Try this in H2 **:

=INDEX(A2:E2,MATCH(INDEX($O$15:$O$21,MATCH(MIN(IF(COUNTIF(B2:F2,"*"&$O$15:$O$21&"*"),$P$15:$P$21)),
IF(COUNTIF(B2:F2,"*"&$O$15:$O$21&"*"),$P$15:$P$21),0))&"*",B2:F2,0))

Array formula, enter with Ctrl+Shift+Enter.

in I2:

=INDEX(B2:F2,MATCH(H2,A2:E2,0))

Thanks for you help leelnich and Phuocam.

Works perfectly.

Cheers
Viren