MATCH with MAX formula question

[Spongie]

I'm hoping someone can help. I have the following formula in a spreadsheet:

=IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low
Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Moderate
Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"High Complexity","")))

The problem I have is that if I have the same return number in both Moderate
and High cells (E22 & I22 respectively), it returns Moderate - ideally I
would like it to return High when the numbers are the same. How do I
accomplish this?

Thanks

 

[Smallweed]

Can't you turn it the other way round, put the High IF comparison first, then
the moderate then the low?

 

[Spongie]

I did try that, but for some reason it's still returning Moderate.

 

[Mike H]

hi,

=IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low",IF(AND(MATCH(MAX(C22:G22),C22:G22,0)=3,E22>G22),"med","High"))

Mike

 

[Lars-Åke Aspelin]

I'm hoping someone can help. I have the following formula in a spreadsheet:

=IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low
Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Moderate
Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"High Complexity","")))

The problem I have is that if I have the same return number in both Moderate
and High cells (E22 & I22 respectively), it returns Moderate - ideally I
would like it to return High when the numbers are the same. How do I
accomplish this?

Thanks

Assuming that I22 is a typo that should be G22, try the following
formula:

=IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(C22),"Low
Complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(E22),"Medium
complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(G22),"High
Complexity")))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Assuming that I22 is a typo that should be G22, try the following
formula:

=IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(C22),"Low
Complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(E22),"Medium
complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=COLUMN(G22),"High
Complexity")))

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

Here is a shorter formula:

=INDEX({"Low","Medium","High"},(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))-COLUMN(A22))/2)&"
Complexity"

Note: This is an array formula that has to be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER

Hope this helps / Lars-Åke