If I have:

Apple Lemon Orange
3 0 1

How do I get Excel to look through the row of data, find the minimum (or
specifically, I am looking for the one and only Zero that would be in the
data), and return the cell above it? In this case, the returned value I am
looking for would be Lemon.

Tried =offset(min(range),-1) but I am guessing Excel won't let me use a
formula for the Reference part of Offset.

With your data in A1:C2 try:

=INDEX(A1:C1,MATCH(0,A2:C2,FALSE))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Shawn" wrote in message
...
If I have:

Apple Lemon Orange
3 0 1

How do I get Excel to look through the row of data, find the minimum (or
specifically, I am looking for the one and only Zero that would be in the
data), and return the cell above it? In this case, the returned value I am
looking for would be Lemon.

Tried =offset(min(range),-1) but I am guessing Excel won't let me use a
formula for the Reference part of Offset.

Sweet. Works perfectly. I always get confused with the ton of options I have
with Excel and when to use which... OFFSET, INDIRECT, MATCH, various
LOOKUPS... Thanks so much for the help.
Shawn

"Sandy Mann" wrote:

With your data in A1:C2 try:

=INDEX(A1:C1,MATCH(0,A2:C2,FALSE))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Shawn" wrote in message
...
If I have:

Apple Lemon Orange
3 0 1

How do I get Excel to look through the row of data, find the minimum (or
specifically, I am looking for the one and only Zero that would be in the
data), and return the cell above it? In this case, the returned value I am
looking for would be Lemon.

Tried =offset(min(range),-1) but I am guessing Excel won't let me use a
formula for the Reference part of Offset.

VLOOKUP() looks down a Column then to the*RIGHT* along that Row, HLOOKUP()
looks along a Row then *DOWN* that Column. To look left or up you are
better to use a combination of INDEX() and MATCH() but when using MATCH() is
the match range is in assending order unless you make the third argument
FALSE.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Shawn" wrote in message
...
Sweet. Works perfectly. I always get confused with the ton of options I
have
with Excel and when to use which... OFFSET, INDIRECT, MATCH, various
LOOKUPS... Thanks so much for the help.
Shawn

"Sandy Mann" wrote:

With your data in A1:C2 try:

=INDEX(A1:C1,MATCH(0,A2:C2,FALSE))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Shawn" wrote in message
...
If I have:

Apple Lemon Orange
3 0 1

How do I get Excel to look through the row of data, find the minimum
(or
specifically, I am looking for the one and only Zero that would be in
the
data), and return the cell above it? In this case, the returned value I
am
looking for would be Lemon.

Tried =offset(min(range),-1) but I am guessing Excel won't let me use a
formula for the Reference part of Offset.

or
=INDEX(H2:J2,0,MATCH(MIN(H3:J3),H3:J3))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shawn" wrote in message
...
If I have:

Apple Lemon Orange
3 0 1

How do I get Excel to look through the row of data, find the minimum (or
specifically, I am looking for the one and only Zero that would be in the
data), and return the cell above it? In this case, the returned value I am
looking for would be Lemon.

Tried =offset(min(range),-1) but I am guessing Excel won't let me use a
formula for the Reference part of Offset.