Multiple conditions IF

[David Lam]

Hello,

Need some help with figuring out this task given to me. So the description is, if a customer under column A has any value below than the following answer is given:

- Column C contain any number equal to or below 7 than "LOW" is given
- Column C contain any number equal to and between 8 and 30 than "MED" is given
- Column C contain any number above or equal to 31 "HIGH" is given

Of course in an event where multiple conditions are met for column C than HIGH get's first priority than MED then LOW. E.g. customer has 7, 15 and 32 in column C than HIGH is given.

Very tricky and I have been trying different ways with IF with failure. Is there any other way?

David

 

[Basavaraj K H]

FYI...

use formula in cell D2 Like below
=LOOKUP(C2,G:H,I:I)
refer attachament.

 

[David Lam]

Nice way of doing it on the raw data but the second condition would make them all HIGH.

"Of course in an event where multiple conditions are met for column C than HIGH get's first priority than MED then LOW. E.g. customer has 7, 15 and 32 in column C than HIGH is given."

If OCEANA APARTMENTS LTD was to be consolidated into 1 row than it would be HIGH. If that makes sense?

 

[Basavaraj K H]

Pls refer attachament, if it is match on second condition

 

[bosco_yip]

Try……

1] Set up a Range Table together with expected grading as in D1:F4

2] Output "name of St" H2, formula copy down :

=IFERROR(INDEX(A$2:A$17,MATCH(0,INDEX(COUNTIF(H$1:H1,A$2:A$17),0),0)),"")

3] Output "Grade" I2, formula copy down :

=IF(H2="","",LOOKUP(INDEX(MAX((A$2:A$17=H2)*B$2:B$17),0),D$2:D$4,F$2:F$4))

Regards
Bosco

 

[David Lam]

Jesus that formula is soo good! I will study this. Thank you