Search for Text and Return Cell Location

**olives** · 06-17-2013, 05:20 PM

I've got a huge spreadsheet full of names that appear in various cells throughout a spreadsheet.

Is there an Excel function that could return the cell location(s) of a particular last name?

Say I had the last name 'DOE' - and just (!) the last name 'DOE' - in A1. Is there a function which if I use it to look at A1 will tell me what other cells in the spreadsheet contain 'DOE' as text within them? Thanks!

**oeldere** · 06-17-2013, 05:24 PM

to find which cells contains DOE you could use CTRL + H (find and replace) and typ DOE.

in this case you will only use find (and not replace).

**AB33** · 06-17-2013, 05:37 PM

Olives,
It could be done by a code. Please attach a sample.

**olives** · 06-17-2013, 06:26 PM

Originally Posted by oeldere

... you could use CTRL + H ...

Thanks!

Originally Posted by AB33

... Please attach a sample....

Thanks, AB33... I have attached a simple spreadsheet with an example. Let me know if you need me to clarify anything! Appreciate it!

example - names.xls

**Ghozi Alkatiri** · 06-17-2013, 07:24 PM

write at M1 (array formula)
{=ADDRESS(MOD(SMALL(IF(RIGHT($A$1:$K$33,LEN($L1))=$L1,COLUMN(A$1:K$33)*10000+(ROW((A$1:K$33)))),COLUMNS($A:A)),10000),TRUNC(SMALL(IF(RIGHT($A$1:$J$33,LEN($L1))=$L1,COLUMN($A$1:$J$33)*10000+(ROW(($A$1:$J$33)))),COLUMNS($A:A))/10000),4)}

copy to right

**olives** · 06-17-2013, 07:53 PM

Originally Posted by Ghozi Alkatiri

write at M1...

That is so cool! It works beautifully! So I just paste it untiL I get an error message meaning there are no more occurences, right? And the range I'm looking at is $A$1:$J$33, so if I expand the range, I just have to replace $A$1:$J$33 with the appropriate range, right? Thanks!