Conditional Lookup?

[kakofoniks]

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 A22 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

 

[Roger Govier]

Hi

Try entering in E2
=INDEX(A11,MATCH(MIN(A22),A22,0))

Regards

Roger Govier

 

[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(A11,MATCH(MIN(IF(A22<0, A22, 1)), A22, 0))
It must be array entered, i.e. Shift+Ctrl+Enter

HTH
Kostis Vezerides

 

[Dave Peterson]

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

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

 

[kakofoniks]

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(A22),A22,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