Lookup formula?
 

I colums a I have values from 1 to 20 repeating seveal times. In column
B I have many different values. I need a formula that with for example
search for the maximum value in column B while column A = 7. Any
Ideas? Thanks.

Karl


--
kwrohde
------------------------------------------------------------------------
kwrohde's Profile: http://www.excelforum.com/member.php...fo&userid=5721
View this thread: http://www.excelforum.com/showthread...hreadid=549456

Lookup formula?
 

Hi Karl,

This worked for me, however it is an array formula so you must use Ctrl
+ Shift + Enter to enter it...

=MAX(IF(A1:A44=7,B1:B44))

Adjust addresses to suit

Ken Johnson

Lookup formula?
 

Ken,

That formula only seems to work if the max value in column b is highest
on the list for coresponding values in column a.


--
kwrohde
------------------------------------------------------------------------
kwrohde's Profile: http://www.excelforum.com/member.php...fo&userid=5721
View this thread: http://www.excelforum.com/showthread...hreadid=549456

Lookup formula?
 

kwrohde wrote:
Ken,

That formula only seems to work if the max value in column b is highest
on the list for coresponding values in column a.

Hi Karl,

Either I misunderstand you problem or the formula doesn't work (as you
say).

Is the value you are after the same as if you filtered column A to only
show the 7s then it's the maximum value that you can see in column B?

Unless I'm missing something (often the case!) that is what the array
formula returns.

Ken Johnson

Lookup formula?
 

Hi Karl,

as per usual, there was something I missed. You were right, it doesn't
return the required maximum.

Try this one, also an array formula...

=MAX(--(A1:A124=7)*B1:B124)

I'm fairly confident this one works (unless I've missed something
again, which is sometimes the case:-))

Ken Johnson

Lookup formula?
 

That does not work entirely.

here is my example


A B C D
2 1123 1 2000
4 5000 2 4566
5 455456 3 1000000
6 450000 4 45645
8 500 5 456
9 1000 6 643
1 550 7 956
3 4566 8 12
7 956 9 10000
1 2000
2 4566
3 1000000
4 45645
5 456
6 643
7 545
8 12
9 10000


the formula in cell d1 is {=MAXA(IF($A1:$A18=$C1,$B1:$B18))}

If you try this example you will see the answers in cell d5, d6, and d8
are in correct.

the formula {=MAX(--($A1:$A18=$C1)*$B1:$B18)} returns the same result.

I found a different solution in another forum that yields correct
results

the formula is:

{=INDEX($B$1:$B$18,MATCH($C1&"
"&MAX(IF($A$1:$A$18=$C1,$B$1:$B$18,0)),$A$1:$A$18& "
"&$B$1:$B$18,0),0)}

this formula works fine but I also need to find for example "the
minimum value in column B while column A = 7"

I assumed that i could substitute max in the formula above with min but
that returns #NA.

Any other ideas?


--
kwrohde
------------------------------------------------------------------------
kwrohde's Profile: http://www.excelforum.com/member.php...fo&userid=5721
View this thread: http://www.excelforum.com/showthread...hreadid=549456

Lookup formula?
 

The references for the ranges need to be absolute. Try...

=MAX(IF($A$1:$A$18=C1,$B$1:$B$18))

and

=MIN(IF($A$1:$A$18=C1,IF($B$1:$B$18<"",$B$1:$B$18 )))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article ,
kwrohde wrote:

That does not work entirely.

here is my example


A B C D
2 1123 1 2000
4 5000 2 4566
5 455456 3 1000000
6 450000 4 45645
8 500 5 456
9 1000 6 643
1 550 7 956
3 4566 8 12
7 956 9 10000
1 2000
2 4566
3 1000000
4 45645
5 456
6 643
7 545
8 12
9 10000


the formula in cell d1 is {=MAXA(IF($A1:$A18=$C1,$B1:$B18))}

If you try this example you will see the answers in cell d5, d6, and d8
are in correct.

the formula {=MAX(--($A1:$A18=$C1)*$B1:$B18)} returns the same result.

I found a different solution in another forum that yields correct
results

the formula is:

{=INDEX($B$1:$B$18,MATCH($C1&"
"&MAX(IF($A$1:$A$18=$C1,$B$1:$B$18,0)),$A$1:$A$18& "
"&$B$1:$B$18,0),0)}

this formula works fine but I also need to find for example "the
minimum value in column B while column A = 7"

I assumed that i could substitute max in the formula above with min but
that returns #NA.

Any other ideas?

Lookup formula?
 

Hi Karl,

Sorry about the confusion, I didn't know you were filling the formula
down a column, necessitating the need for a combination of absolute and
relative references.

Thanks to Dominic for clearing things up.

Ken Johnson