Jul 21 2020 11:23 AM
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?
Jul 21 2020 11:28 AM
Jul 21 2020 11:34 AM
Jul 21 2020 11:50 AM
or so :)
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)
Jul 21 2020 11:59 AM
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 |
Jul 21 2020 12:19 PM
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)
Jul 21 2020 12:53 PM
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!
Jul 22 2020 09:04 AM
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.