Lookup a value, return the cell address containing that value

[Plokimu77]

Hello Forum,

I need help with a formula that will tell me in what cell was a value found.

I apologies that I cant create a table to display my request. (I am at work)


The value I am looking for is in cell A1
for example if it is the word apple.

I would like to have the formula in B1 and it will tell me
that the word apple is cell E45. (Row E contains the range I am looking thru)

Can someone please help. Thank you

 

[Mike LH]

Hi,

=ADDRESS(MATCH(A1,E1:E100,0),5,4)

 

[Plokimu77]

Great!!

Thank you..

What would I need to change if I needed to look on sheet2 column 1.

Thanks for the help!

 

[Mike LH]

Hi,

Try this


=ADDRESS(MATCH(A1,Sheet2!A1:A100,0),1,4)


To help you adapt the formula let me explain.

A1 is the location of the value to find. The lookup value
Sheet2!A1:A100 is where to look for the value
0 Tells the formula to find only an exact match.
The above 2 lines work out the row part of the address

1 Represents column 1 for the address because we know it will be in column 1 because that's where we're looking.

4 tells the formula to return a relative address

 

[Plokimu77]

Cool.

Thanks for the Intel, I see it now.