Conditional Lookup?

[kakofoniks@yahoo.com]

Hi there:

I am trying to accomplish the following. Given the following list of
data:

A B C D E
----------------------------------------------
1 | Num1 Num2 Num3 Num4
2 | -2 0 -3 -1

where row 1 contains the data header, I would like to set up a formula
in cell E2 which looks through the range A2:D2 for the most -ve number
and returns its row header from row 1. I'm not having much luck with
the LOOKUP function (unless the values in row 2 are sorted in
increasing order).

TIA,
K

[vezerid]

K,
is it the most negative number you are seeking? And it has to be
negative? If so, this formula will produce the correct header:
=INDEX(A1:D1,MATCH(MIN(IF(A2:D2<0, A2:D2, 1)), A2:D2, 0))
It must be array entered, i.e. Shift+Ctrl+Enter

HTH
Kostis Vezerides

[Roger Govier]

Hi

Try entering in E2
=INDEX(A1:D1,MATCH(MIN(A2:D2),A2:D2,0))

Regards

Roger Govier


kakofoniks@yahoo.com wrote:
> Hi there:
>
> I am trying to accomplish the following. Given the following list of
> data:
>
> A B C D E
> ----------------------------------------------
> 1 | Num1 Num2 Num3 Num4
> 2 | -2 0 -3 -1
>
> where row 1 contains the data header, I would like to set up a formula
> in cell E2 which looks through the range A2:D2 for the most -ve number
> and returns its row header from row 1. I'm not having much luck with
> the LOOKUP function (unless the values in row 2 are sorted in
> increasing order).
>
> TIA,
> K
>

[Dave Peterson]

Maybe this one:
=INDEX($A$1:$D$1,MATCH(MIN(A2:D2),A2:D2,0))

or if you really want to check for negatives:
=IF(COUNTIF(A2:D2,"<"&0)=0,"no negatives",
INDEX($A$1:$D$1,MATCH(MIN(A2:D2),A2:D2,0)))
(all one cell)

kakofoniks@yahoo.com wrote:
>
> Hi there:
>
> I am trying to accomplish the following. Given the following list of
> data:
>
> A B C D E
> ----------------------------------------------
> 1 | Num1 Num2 Num3 Num4
> 2 | -2 0 -3 -1
>
> where row 1 contains the data header, I would like to set up a formula
> in cell E2 which looks through the range A2:D2 for the most -ve number
> and returns its row header from row 1. I'm not having much luck with
> the LOOKUP function (unless the values in row 2 are sorted in
> increasing order).
>
> TIA,
> K

--

Dave Peterson

[kakofoniks@yahoo.com]

Thanks! Hadn't realised that I would need an array formula.. but it
works perfectly.

K

[Gaj Vidmar]

One of the many possible solutions should be

=offset(A1,0,match(min(A2:D2),A2:D2,0)-1)

Note that if there are multiple minimuma, this will, of course, return the
label above the first one of them (looking from the left side rightwards),
but handling that (e.g. with a warning) would require VBA programming, and
is probably also beyond what you need.

Regards,
Gaj Vidmar

<kakofoniks@yahoo.com> wrote in message
news:1132152554.412627.161730@o13g2000cwo.googlegroups.com...
>
> Hi there:
>
> I am trying to accomplish the following. Given the following list of
> data:
>
> A B C D E
> ----------------------------------------------
> 1 | Num1 Num2 Num3 Num4
> 2 | -2 0 -3 -1
>
> where row 1 contains the data header, I would like to set up a formula
> in cell E2 which looks through the range A2:D2 for the most -ve number
> and returns its row header from row 1. I'm not having much luck with
> the LOOKUP function (unless the values in row 2 are sorted in
> increasing order).
>
> TIA,
> K