Conditional Lookup?
 

Hi the

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

Conditional Lookup?
 

Hi

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

Regards

Roger Govier


wrote:
Hi the

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

Conditional Lookup?
 

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

Conditional Lookup?
 

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)

wrote:

Hi the

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

Conditional Lookup?
 

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

K

Conditional Lookup?
 

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

wrote in message
oups.com...

Hi the

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