Lookup formula?

[kwrohde]

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

[Ken Johnson]

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

[kwrohde]

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.

[Ken Johnson]

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

[Ken Johnson]

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

[kwrohde]

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?

[Domenic]

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 <[email protected]>,
kwrohde <[email protected]> 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?

[Ken Johnson]

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