Forum Discussion
Finding a word in a worksheet
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 ca...
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?
or so 🙂
Matrix 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.
A B C D E F G 1 Green 2 3 Boy 4 Apple 5 Cat 6 House 7 Thomas So in the first sheet it looks like this:
Green A1 Boy
E3 Apple B4 Thomas F7 Cat G5 House C6 If name the region with words as Range (performance is significantly depends on its size)
formula could be
=ADDRESS(SUMPRODUCT((Range=$C3)*ROW(Range))-@ROW(Range)+1, SUMPRODUCT((Range=$C3)*COLUMN(Range))-@COLUMN(Range)+1, 4)
