Conditional Lookup?

  • Thread starter Thread starter kakofoniks
  • Start date Start date
K

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 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
 
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
 
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)
 
Thanks! Hadn't realised that I would need an array formula.. but it
works perfectly.

K
 
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
 
Back
Top