Can I Prioritize or Wieght an IF Statement?

[jjbNana]

Hi, all,

I have a large spreadsheet, with 2000+ rows/records. I categorized each record with a value of L, M, or H. At the top of the sheet (cell AM4), I want to report if an "H", an "M" or an "L" has been entered in the categorization column, in that order. In other words, I want to weigh-out or boil-up the category, so an "H" would outweigh an "M" or "L" and so on. The formula I have thought of (but obviously does not work) is:

=IF(AM5:AM2933="H","H")*IF(AM5:AM2933="M","M")*IF(AM5:AM2933="L","L")

I thought that if I strung the IF statements together, then each would report it's TRUE result (if found), or look to the next IF statement if the value was FALSE. Clearly, my logic was not sound.

Thank you in advance for anyone who may be able to help.

 

[Scott Huish]

so an "H" would outweigh an "M" or "L" and so on.

and return what value exactly?

 

[Jonmo1]

Welcome to the board...

If I understand right...

Try
=LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"})

 

[jjbNana]

and return what value exactly?

The value would be an H, an M or an L. I would then conditionally format the cell to fill red for H, yellow for M and green for L, so as to see the overall categorization at a glance of the sheet.

 

[jjbNana]

Welcome to the board...

Thanks.

Try
=LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"})

The result "#N/A" came back, like it didn't find an L, M or H in any of those cells.

 

[Jonmo1]

Thanks.

The result "#N/A" came back, like it didn't find an L, M or H in any of those cells.

Which version of XL do you have?

XL2007 or Higher, try

=IFERROR(LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"}),"")


Pre 2007, it will be a tad longer..

=IF(ISNA(LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"})),"",LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"}))

 

[jjbNana]

Which version of XL do you have?

XL2007 or Higher, try

=IFERROR(LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"}),"")

I have 2007, and your 1st suggestion worked like a charm. I initially pasted it into the wrong version of the spreadsheet. I have two with variable columns, and I put it in the one where "AX" was the right column. So, I fixed the column reference, and it worked. Thank you.

You're the best!

 

[Jonmo1]

Glad to help, thanks for the feedback..

If you also have 2003, you might be well served to use the version compatible with both versions...

Here's another way to do it that is a bit shorter (works in either version of XL)

=LOOKUP("Z",CHOOSE({1,2},"",LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"})))

 

[jjbNana]

Glad to help, thanks for the feedback..

If you also have 2003, you might be well served to use the version compatible with both versions...

Here's another way to do it that is a bit shorter (works in either version of XL)

=LOOKUP("Z",CHOOSE({1,2},"",LOOKUP(2,1/ISNUMBER(MATCH({"L","M","H"},AM5:AM2933,0)),{"L","M","H"})))

It even picked up the conditional formatting I already had on the entries on each row.

Thanks again!