excel lookup returning reference

1
votes

Excel has a "lookup" function that finds a specified value in a row or column and returns a corresponding value from another column.

But is there a lookup that returns the reference of the found cell? I have a case where I have a column of dates, and I want to search the list for a given date and then return the reference -- A23 or whatever -- of that cell.

2
excelexcel-formula

2 Answers

2
votes

I believe you're looking for the Find method in VBA?

.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

http://msdn.microsoft.com/en-us/library/aa195730(office.11).aspx

5
votes

Can also be done using formula's only.

eg Search range A1:A10 for value in B1, return address

=CELL("address",OFFSET($A$1,MATCH(B1,$A$1:$A$10),0))