Using IF, AND and MAX in one formulae

[Steve E]

I am trying to create a formula that does the following:

Lookup a range from 3 separate columns. If the criteria are met, then I want Excel to pick the max value from another column range and place the result in another cell.

Is this possible in one complete formula?

I am currently using something like this, which returns the MAX value for all the data, rather than the criteria:

=IF(AND(C6:C199="SO",AND(E6:E199="S",F6:F199="East"),MAX(AG6:AG199)).

Using Excel 2000.

Thanks, Steve.

 

[T. Valko]

I am trying to create a formula that does the following:

Lookup a range from 3 separate columns. If the criteria are met, then I want Excel to pick the max value from another column range and place the result in another cell.

Is this possible in one complete formula?

I am currently using something like this, which returns the MAX value for all the data, rather than the criteria:

=IF(AND(C6:C199="SO",AND(E6:E199="S",F6:F199="East"),MAX(AG6:AG199)).

Using Excel 2000.

Thanks, Steve.

Try it like this...

Array entered**:

=MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Steve E]

Thank you! It works great. S.

 

[Steve E]

I am trying to create a formula that does the following:

Lookup a range from 3 separate columns. If the criteria are met, then I want Excel to pick the max value from another column range and place the result in another cell.

Is this possible in one complete formula?

I am currently using something like this, which returns the MAX value for all the data, rather than the criteria:

=IF(AND(C6:C199="SO",AND(E6:E199="S",F6:F199="East"),MAX(AG6:AG199)).

Using Excel 2000.

Thanks, Steve.

One other thing. Now I got this working, would I also be able to do this:

The formula as you suggest above, ie: {=MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199))))}
chooses the max value % and places it in a cell on another sheet. Column A (A6:A199) where the above data resides also contains the descriptions for the max value %. Is there a way of applying a lookup that finds the Max value % and then puts the contents of column A in another cell in the other sheet.

Difficult to explain, but it's kind of a lookup on the result of a Max value.

Thanks, S.

 

[T. Valko]

Thank you! It works great. S.

You're welcome!

 

[T. Valko]

One other thing. Now I got this working, would I also be able to do this:

The formula as you suggest above, ie: {=MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199))))}
chooses the max value % and places it in a cell on another sheet. Column A (A6:A199) where the above data resides also contains the descriptions for the max value %. Is there a way of applying a lookup that finds the Max value % and then puts the contents of column A in another cell in the other sheet.

Difficult to explain, but it's kind of a lookup on the result of a Max value.

Thanks, S.

Hmmm...

I think I understand...

Book1
	A	B
2	Data1	25
3	Data2	56
4	Data3	10
5	Data4	66
6	Data5	80
7	Data6	72
8	Data7	17
9	Data8	43
10	Data9	39
Sheet1

Return the value from column A that corresponds to the max value in column B?

What if there is more than one instance of the max value...

Book1
	A	B
2	Data1	25
3	Data2	56
4	Data3	80
5	Data4	66
6	Data5	80
7	Data6	72
8	Data7	17
9	Data8	43
10	Data9	80
Sheet1

 

[Steve E]

Hmmm...

I think I understand...

Book1
	A	B
2	Data1	25
3	Data2	56
4	Data3	10
5	Data4	66
6	Data5	80
7	Data6	72
8	Data7	17
9	Data8	43
10	Data9	39
Sheet1

Return the value from column A that corresponds to the max value in column B?

What if there is more than one instance of the max value...

Book1
	A	B
2	Data1	25
3	Data2	56
4	Data3	80
5	Data4	66
6	Data5	80
7	Data6	72
8	Data7	17
9	Data8	43
10	Data9	80
Sheet1

The data I'm using only has one instance, but that may be coincidental. The data changes every 4 weeks, so there is a chance of more than one instance. However, for the purpose of this (your assumption of my query is correct!) we'll work on one instance only. Thanks. S.

 

[T. Valko]

The data I'm using only has one instance, but that may be coincidental. The data changes every 4 weeks, so there is a chance of more than one instance. However, for the purpose of this (your assumption of my query is correct!) we'll work on one instance only. Thanks. S.

Ok, this should do it.

Array entered**:

=INDEX(A6:A199,MATCH(MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199)))),AG6:AG199,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Steve E]

Ok, this should do it.

Array entered**:

=INDEX(A6:A199,MATCH(MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199)))),AG6:AG199,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks, it does work, however, I have now used the formula elsewhere in the spreadsheet, where the max value resides in a different column (G9:G199), instead of AG9:AG199. The rest of the formula is identical, however it returns a weird result, which doesn't correspond to the IF criteria provided. The column containing the max value is formatted the same (percent), so now I really am confused. I presume the formula doesn't act like Lookups, where data has to be in certain columns? Any ideas? Steve.

 

[T. Valko]

Thanks, it does work, however, I have now used the formula elsewhere in the spreadsheet, where the max value resides in a different column (G9:G199), instead of AG9:AG199. The rest of the formula is identical, however it returns a weird result, which doesn't correspond to the IF criteria provided. The column containing the max value is formatted the same (percent), so now I really am confused. I presume the formula doesn't act like Lookups, where data has to be in certain columns? Any ideas? Steve.

There may be duplicate instances of the max value but one of them doesn't meet all the other conditions.

Try something like this.

Still array entered:

=INDEX(A6:A199,MATCH(MAX(IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199)))),IF(C6:C199="SO",IF(E6:E199="S",IF(F6:F199="East",AG6:AG199))),0))