VLOOKUP That needs to give result based on a ranking list

[Sanecrazy]

Hi All

I have a database which has a list of referance numbers along with a problem listed next to it. In some cases I may get the same ref number in the database but with a different problem. When i use vlookup i would like to bring back the highest priority ranking problem for that ref number in the list.

I have attempted to use vlookup and if to no avail

Please could you help? ive attached a example for you to look at

Cheers

Sane

[Andrew-R]

Assuming that the status rank is always a single number at the start of the string then this should work:

=INDEX(D:D,MATCH(MIN(INDEX(IFERROR(VALUE(LEFT(D1:D35,(C1:C35=F2))),999),0)),INDEX(IFERROR(VALUE(LEFT(D1:D35,(C1:C35=F2))),999),0),0))

This is an array formula and must be entered using Ctrl-Shift-Enter, not just enter.

[Haseeb Avarakkan]

If you have RANKS sorted in A2:A10, try in H2 with CTRL+SHIFT+ENTER, rather than just ENTER

=INDEX(A$2:A$10,MATCH(1,IF(ISNUMBER(MATCH(A$2:A$10,IF(C$2:C$35=F2,D$2:D$35),0)),1),0))

Then copy down.