Compare Cells of Text and Return Highest Value based on a Customiz
 

Similar to the MAX function where it compares several cells of number and
returns the highest value, it is possible to compare several cells of text
and return the highest value based on a customized list.
For example the customized list could be: Critical, High, Moderate and lower
Col A Col B Col C Col with formula to return highest value of Col A, B, C
Crit High Low Crit
Low Mod Low Mod
Low Mod High High

Thanks,
Rod

Compare Cells of Text and Return Highest Value based on a Customiz
 

Enter

=LOOKUP(MAX(LOOKUP(A2:C2,{"Crit","High","Low","Mod "},{4,3,1,2})),{1,2,3,4},{"Low","Mod","High","Crit "})

in cell D2 as an array formula confirmed with Ctrl+Shift+Enter and drag it
down!

Regards,
Stefi

€žRod€� ezt Ã*rta:

Similar to the MAX function where it compares several cells of number and
returns the highest value, it is possible to compare several cells of text
and return the highest value based on a customized list.
For example the customized list could be: Critical, High, Moderate and lower
Col A Col B Col C Col with formula to return highest value of Col A, B, C
Crit High Low Crit
Low Mod Low Mod
Low Mod High High

Thanks,
Rod

Hi Stefi,

Thank you for the formula below.
It seems though that it doesn't work when the lookup values are not in alphabetical order?

As an example of what I was trying to do:
=LOOKUP(MAX(LOOKUP(B2:E2,{"M","G","W","X"},{4,3,2, 1})),{1,2,3,4},{"X","W","G","M"})

Where M is supposed to be the highest value, G as 2nd, W as 3rd, and X as the lowest value. The problem is between M and G wherein the results always give a G even if an M is in one of the cells...and still a G even if there is no G in that particular row. I think the reason is because G is always first over M in the alphabet.

Feature Name 1 2 3 4 Result
Feature A M G W X G
Feature B M M W X G
Feature C X W W W W
Feature D X X X X X
Feature E M M M M G


When I changed G with O, it worked.

Do you have any other ideas?

Also, if I am searching through the same cell across 7 tabs (and not from a row/column of cells in the same tab), is this possible?

Thank you and best regards,
Maria