Finding a word in a worksheet

Copper Contributor

Hi!

 

I am trying to find a specific word that is in a worksheet and return the cell address.  The word will only be in the sheet 1 time, but it can be located anywhere in the sheet.  Because it can be anywhere, I haven't been able to use any of the traditional lookup formulas.  Does anyone have any suggestions?

8 Replies
STRG + F
for search,
write the word you search...and if there..excel find it :)

I would be happy to find out if I could help.


Nikolino
I know I don't know anything (Socrates)
Hi, thank you but it needs to be a formula as there is a long list of words that need to be found and then those references need to be used in a longer formula. Not sure if this is possible?

@TM20204 

 

find_word.JPG

@NikolinoDE 

 

or so :)

 

Matrix FormelMatrix Formel

Attention, matrix formula!
Don't type the braces {}
but the cell with
Shift + Ctrl + Enter
leave alone instead of enter.

 

I would be happy to find out if I could help.

 

 

Nikolino

I know I don't know anything (Socrates)

 

Hi I'm not sure this will work.  I'll try to be clearer.  On one sheet I have a list of words:

 

Green

Boy

Apple

Thomas

Cat

House

 

On another sheet, I have a giant data set where the words can be anywhere (There are no blank cells but have left some blank to illustrate my point).  I need to know what cell each word is in. 

 

 ABCDEFG
1Green      
2       
3    Boy  
4 Apple     
5      Cat
6  House    
7     Thomas 

 

 

So in the first sheet it looks like this:

 

GreenA1

Boy

E3
AppleB4
ThomasF7
CatG5
HouseC6

 

 

 

@NikolinoDE 

@TM20204 

If name the region with words as Range (performance is significantly depends on its size)

image.png

formula could be

=ADDRESS(SUMPRODUCT((Range=$C3)*ROW(Range))-@ROW(Range)+1,
         SUMPRODUCT((Range=$C3)*COLUMN(Range))-@COLUMN(Range)+1,
4)

Thank you this worked! I'm not quite sure how it's working but I was able to modify the areas I needed.  Is there a link somewhere that I can learn how the sumproduct is working in this formula?

 

Thank you so much!

 

 

 

@Sergei Baklan 

@TM20204 

Basic formula is explained here Get location of value in 2D array , the only it shall be slightly modified if you are on Excel wit dynamic arrays.