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
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:
or=INDEX(Statistics,MATCH($E$27,StatCol_1,0),8)
• This formula returns the description of the reference...eg "$L$17"
=CELL("address",INDEX(Statistics,MATCH($E$27,StatCol_1,0),8))
Does that help...or do you need something else?
Does this work for you?
=CELL("Address",OFFSET(Statistics,MATCH($E$27,OFFSET(Statistics,0,0,,1),0)-1,7,1,1))
Many thanks for your help guys, Andy Pope, your solution was great for what I needed!
Best wishes,
Milky
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks