Forum Discussion
Finding a word in a worksheet
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)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!
