MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup


Posted by Jeff on February 07, 2002 9:21 AM

I want to lookup a given value in a column and return the cell's reference. Ex. In column D I want to look for the first cell containing 3-1 when it finds it I want the return to be which cell 3-1 is in, ex D552.

I cannot find the right combination of formulas to make this work.

Please help.


Posted by Mark W. on February 07, 2002 9:28 AM

=ADDRESS(MATCH("3-1",D:D),4)...

or, if 3-1 is a date...

=ADDRESS(MATCH(DATEVALUE("3-1"),D:D),4)

Posted by Mark W. on February 07, 2002 9:29 AM

Use =ADDRESS(MATCH(DATEVALUE("3-1"),D:D),4,4) for a relative reference [nt]

Posted by Jeff on February 07, 2002 9:40 AM

Re: =ADDRESS(MATCH("3-1",D:D),4)...

Thanks so far, how do I make this work if I want to be able to type what I want to lookup into a cell. Say I want to type the 3-1 in cell e2 or am I going to have to change the formula every time I want to lookup something?

Posted by Mark W. on February 07, 2002 9:54 AM

=ADDRESS(MATCH(E2,D:D),4,4) [nt]