Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two cells in two different files and return answer | Excel Discussion (Misc queries) | |||
Highest 3 in 10 Based on Consecutive Cells | Excel Discussion (Misc queries) | |||
Compare two cells and return certain value in third cell | Excel Worksheet Functions | |||
How do I return the highest value in a range of cells | Excel Worksheet Functions | |||
Compare 2 rows of text and return adj value | Excel Worksheet Functions |