Lookup Cell Address

I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my
table starts in A1 and looks like this...

3
4
5
6
7

When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.

On Thu, 11 Feb 2010 17:47:02 -0800, hmmm
wrote:

I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my
table starts in A1 and looks like this...

3
4
5
6
7

When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.

Use the MATCH worksheet function.

If you just want to return a 1, then:

D1: 3

MATCH(D1,A1:A5)

If you want to return A1, then add the ADDRESS function:

=ADDRESS(MATCH(D1,A1:A5),1,4)
--ron

Take a look MATCH function in help menu


"hmmm" wrote:

I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my
table starts in A1 and looks like this...

3
4
5
6
7

When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.

Just a note about using MATCH as has been suggested...

MATCH returns the *relative* position of the lookup_value within the
lookup_array. The actual cell address is irrelevant as to how MATCH works
and the value it returns.

So, do you want the *relative position* or the *actual row number* as a
result?

Lookup_value = 3

A1 = 3
A2 = 5
A3 = 7

=MATCH(3,A1:A3,0) = 1

A20 = 3
A21 = 5
A22 = 7

=MATCH(3,A20:A22,0) = 1

In this second example, do you want a result of 1 or 20?

--
Biff
Microsoft Excel MVP


"hmmm" wrote in message
news
I'm trying to lookup a value in a list, but return the cell address (or
row
number) of where the value was found instead of the value itself. So if
my
table starts in A1 and looks like this...

3
4
5
6
7

When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.

On Thu, 11 Feb 2010 21:19:09 -0500, Ron Rosenfeld
wrote:

On Thu, 11 Feb 2010 17:47:02 -0800, hmmm
wrote:

I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my
table starts in A1 and looks like this...

3
4
5
6
7

When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.

Use the MATCH worksheet function.

If you just want to return a 1, then:

D1: 3

MATCH(D1,A1:A5)

If you want to return A1, then add the ADDRESS function:

=ADDRESS(MATCH(D1,A1:A5),1,4)
--ron

An oversight:

The MATCH part of the above formulas should be:

=match(d1,a1:a5,0) in order to match exactly.
--ron