Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

compare qualitative values

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Compare qualitative values in 2 cells

Compare qualitative values in 2 cells

ResolvedVersion 2010

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

Mon 30 Sep 2019: Automatically marked as resolved.

Excel tip:

Use the Ctrl-key for quick navigation in Excel 2010

If you want to move quickly to the right, left, top or bottom of your spreadsheet, just press Ctrl and one of the arrow keys. If you want to then select all the data in that particular row or column, hold down the Shift key and press Ctrl and an arrow key.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.