Returning the result of a VLOOKUP as a cell reference?

**MilkyQuail** · 02-26-2011, 10:46 AM

Hi Guys,

I've had a look at a few similar problems to mine, but I don't seem able to relate the solutions to those problems to my situation.

I have a cell with the following formula: =VLOOKUP($E$27,Statistics,8,TRUE)

Which returns the value of a lookup without any trouble.

However, I'd like for it to return the cell reference of that lookup, rather than the value in the cell.

Is this possible with my formula?

Thanks for your time,

Milky

Edit - Crosspost: http://www.mrexcel.com/forum/showthr...96#post2626996

**Ron Coderre** · 02-26-2011, 11:04 AM

Using =VLOOKUP($E$27,Statistics,8,TRUE)

If you want the reference of the cell that is in the 8th column of Statistics for the matched item from E27...Here are a couple approaches:
• Create a range name for the 1st column of Statistics....eg StatCol_1

• This formula actually refers to the cell....eg cell L17:

Please Login or Register  to view this content.

or
• This formula returns the description of the reference...eg "$L$17"

Please Login or Register  to view this content.

Does that help...or do you need something else?

**Andy Pope** · 02-26-2011, 11:08 AM

Does this work for you?

=CELL("Address",OFFSET(Statistics,MATCH($E$27,OFFSET(Statistics,0,0,,1),0)-1,7,1,1))

**MilkyQuail** · 02-26-2011, 03:45 PM

Many thanks for your help guys, Andy Pope, your solution was great for what I needed!

Best wishes,

Milky

Returning the result of a VLOOKUP as a cell reference?

LinkBack

Thread Tools

Rate This Thread

Display

Returning the result of a VLOOKUP as a cell reference?

Re: Returning the result of a VLOOKUP as a cell reference?

Re: Returning the result of a VLOOKUP as a cell reference?

Re: Returning the result of a VLOOKUP as a cell reference?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions