Sort then lookup
 

I am trying to create a lookup function that locates the max value in
column A and then identifies the value in the same row of column B. But
the trick is that I need the max value in column A that has a value in
the same row as column B that is greater than zero. So there are 2
rules that need to be met in the lookup function.

A B

2.89 -206,072,627.00
3.08 -173,714,739.00
13.52 -183,189,686.00
15.50 -149,160,742.00
43.84 -32,308,509.00
357,778,994.23 10,251,638.00
359,474,629.25 0.00


In the example above I would be trying to capture the value
10,251,638.00 via some lookup function.

Any thoughts on how to do this?

Sort then lookup
 

Try this *array* formula:

=LOOKUP((MAX(IF(B1:B7<0,A1:A7))),A1:B7)

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steve" wrote in message
oups.com...
I am trying to create a lookup function that locates the max value in
column A and then identifies the value in the same row of column B. But
the trick is that I need the max value in column A that has a value in
the same row as column B that is greater than zero. So there are 2
rules that need to be met in the lookup function.

A B

2.89 -206,072,627.00
3.08 -173,714,739.00
13.52 -183,189,686.00
15.50 -149,160,742.00
43.84 -32,308,509.00
357,778,994.23 10,251,638.00
359,474,629.25 0.00


In the example above I would be trying to capture the value
10,251,638.00 via some lookup function.

Any thoughts on how to do this?