Caben has attended:
Excel Advanced course
Compare qualitative values in 2 cells
To the attention of Andy
Hi
There are 2 cells which can each return the following qualitative values, i.e. Low, Medium, High, Very High (in ascending order of criticality). I need to compare the 2 cells and return (in another cell) the higher of the two values in terms criticality.
Thanks very much
Caben
RE: Compare qualitative values in 2 cells
Hello Caben,
Thank you for this interesting question. I would set it up as follows:
Because Excel likes numbers, I would create a lookup table in 4 cells:
Low 1
Medium 2
High 3
Very High 4
Next I would create two Vlookups similar to these:
=VLOOKUP(A1,G1:H4,2,FALSE)
=VLOOKUP(B1,G1:H4,2,FALSE)
* A1 and B1 contain the Low, Medium, High or Very High text entries.
* G1:H4 contains my lookup table.
The Vlookups give the text entries numeric values. Next, I would write a nested IF function as follows:
=IF(C1>D1,C1,IF(D1>C1,D1,IF(C1=D1,C1)))
*C1 and D1 contain the Vlookups from earlier (1, 2, 3 or 4).
The nested IF function tests which cell contains the highest priority value.
Finally, I would create a CHOOSE function, which will show the result of the nested IF as text (Low, Medium, High, Very High):
=CHOOSE(E1,"Low","Medium","High","Very High")
*E1 contains the result of the nested IF function (a numeric value).
The CHOOSE function will show the highest priority text from the two initial cells.
You can hide the columns containing the formulas afterwards.
I hope this makes sense!
Kind regards
Marius Barnard
STL
