Returning the result of a VLOOKUP as a cell reference?

Hello Miky,

I dont know the setup of your data but try something like this:

Assume the following table in A1 - C4.
Row Headings
A1: Salesman
B1: Product
C1: Price

names form A2 to A4
George
Bill
John

products form B2 to B4
K1
K2
K3

price from C2 to C4
12
21
44

Lets say you want to lookup price for Bill. You would go:

lookup(bill,a1:c4,3,false). To return the cell reference ie:C3 (which corresponds to the price of 21) do the following:

Name the ranges A2:C2 A3:C3 A4:C4 with whatever name you want (i used the originall names George - Bill - John)

Now put in cell E2 your lookup value ie:Bill and in F2 the following:

=ADDRESS(MATCH(E2,A1:A4,0),MATCH(VLOOKUP(E2,A1:C4,3,FALSE),INDIRECT(E2),0))

It should give you $C$3

George

MilkyQuail said:

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.excelforum.com/excel-wor...-vlookup-as-a-cell-reference.html#post2479923

Click to expand...

MilkyQuail said:

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.excelforum.com/excel-wor...-vlookup-as-a-cell-reference.html#post2479923

Click to expand...

Try...

=CELL("Address",INDEX(Statistics,MATCH($E$27,INDEX(Statistics,0,1),1)))

Many thanks for your help guys, you've been great in helping me find a solution!

Best wishes,

Milky