I'm sure this is easy..

[Spacebug]

I simply want to find the cell reference that contains some specified data..

Harry	a	21
Henry	b	43
Alice	c	54
John	d	65
Mike	e	76
Ian	f	43
Pete	g	32
Dolly	h	54

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

For example, how would I go about finding the cell reference of the cell containing "Alice"? It may not always be in the same place, so I need to search for it.

Thanks,
John

 

[Oeldere]

d1=Vlookup("Alice",$a$1:$c$8,2,0)

 

[a massive zebra]

Oeldere, surely that would just bring back the value in column B, i.e. c in Alice's case? If the names are in column A and you want to bring back the cell reference for this name, try =ADDRESS(MATCH("Alice",$A$1:$A$8,0),COLUMN(A1)). If you want to bring back the column entered in column B and the row entered in column C, try =VLOOKUP("Alice",$A$1:$C$8,2,FALSE)&VLOOKUP("Alice",$A$1:$C$8,3,FALSE). If you wanted to bring back the absolute values rather than a cell reference, nest these formulas within indirect: =INDIRECT(ADDRESS(MATCH("Alice",$A$1:$A$8,0),COLUMN(A1))) or INDIRECT(VLOOKUP("Alice",$A$1:$C$8,2,FALSE)&VLOOKUP("Alice",$A$1:$C$8,3,FALSE))

 

[Spacebug]

d1=Vlookup("Alice",$a$1:$c$8,2,0)

Thanks for the quick reply.

However, this isn't what I meant. Your example returns 'c'. What I want it to return is A3 as this is the location of 'Alice' in this particular table.

 

[Spacebug]

Oeldere, surely that would just bring back the value in column B, i.e. c in Alice's case? If the names are in column A and you want to bring back the cell reference for this name, try =ADDRESS(MATCH("Alice",$A$1:$A$8,0),COLUMN(A1)). If you want to bring back the column entered in column B and the row entered in column C, try =VLOOKUP("Alice",$A$1:$C$8,2,FALSE)&VLOOKUP("Alice",$A$1:$C$8,3,FALSE). If you wanted to bring back the absolute values rather than a cell reference, nest these formulas within indirect: =INDIRECT(ADDRESS(MATCH("Alice",$A$1:$A$8,0),COLUMN(A1))) or INDIRECT(VLOOKUP("Alice",$A$1:$C$8,2,FALSE)&VLOOKUP("Alice",$A$1:$C$8,3,FALSE))

Perfect, thanks!

 

[Oeldere]

@a massive zebra

thanks for pointing me on this built in formula (the red text); realy appreciated.

=ADDRESS(MATCH("Alice",$A$1:$A$8,0),COLUMN(A1))

 

[a massive zebra]

Glad to be of assistance!