look for a word in a set of cells (multiple columns X multiple rows)

[Osvaldo Palmeiro]

Hi all.

Let's say I have one word in each cell in range A1:E10.
Just one cell in that range has the word "cat".
Is there a fórmula that will return the reference (column and row) from that cell that holds "cat" ?
Many thanks in advance

 

[RasGhul]

Try this

=CONCATENATE("Column "&SUMPRODUCT(($A$1:$E$10="cat")*COLUMN($A$1:$E$1)-COLUMN($A$1)+1)&", Row "&SUMPRODUCT(($A$1:$E$10="cat")*ROW($A$1:$A$10)-ROW($A$1)+1))

 

[RasGhul]

Or if you meant the actual cell reference;

Book1
	A	B	C	D	E
1	dogs	dogs	dogs	dogs	dogs
2	dogs	dogs	dogs	dogs	dogs
3	dogs	dogs	dogs	dogs	dogs
4	dogs	cat	dogs	dogs	dogs
5	dogs	dogs	dogs	dogs	dogs
6	dogs	dogs	dogs	dogs	dogs
7	dogs	dogs	dogs	dogs	dogs
8	dogs	dogs	dogs	dogs	dogs
9	dogs	dogs	dogs	dogs	dogs
10	dogs	dogs	dogs	dogs	dogs
11
12	Criteria
13	cat
14
15	Column 2, Row 4
16
17	$B$4
Sheet1

Cell Formulas
Range	Formula
A15	=CONCATENATE("Column "&SUMPRODUCT(($A$1:$E$10="cat")*COLUMN($A$1:$E$1)-COLUMN($A$1)+1)&", Row "&SUMPRODUCT(($A$1:$E$10="cat")*ROW($A$1:$A$10)-ROW($A$1)+1))
A17	=CELL("address",INDEX($A$1:$E$10,SUMPRODUCT(($A$1:$E$10=$A$13)*ROW($A$1:$A$10)-ROW($A$1)+1),SUMPRODUCT(($A$1:$E$10=$A$13)*COLUMN($A$1:$E$1)-COLUMN($A$1)+1)))

 

[Osvaldo Palmeiro]

Hi, @RasGhul.
Thank youuuuu.
Wonderfull, both formulas work like a rocket.
Thank you so much.