Return row and column of value in table

[zunkman]

I have a 4x4 table with the numbers 1-16 in random locations in that table. (Think like a Bingo Card.)

If I choose one of those numbers (12 for example) I want to know which row and which column contain that number.

I'm pretty fair at Excel and Google Sheets but this one has me stumped. I'd like this to be scalable as well, to handle any size table.

Can anyone offer any suggestions? Thanks!!!

Zunkman

 

[alansidman]

Book2
	A	B	C	D	E	F
1	5	7	9	2		4
2	6	8	10	3
3	11	15	1	4		$D$3
4	12	14	13	16
Sheet1

Cell Formulas
Range	Formula
F3	F3	=ADDRESS(SUMPRODUCT(--(A1:D4=F1)*ROW(A1:D4)),SUMPRODUCT(--(A1:D4=F1)*COLUMN(A1:D4)))

Your lookup is in F1 and the result is in F3

 

[zunkman]

=ADDRESS(SUMPRODUCT(--(A1:D4=F1)*ROW(A1:D4)),SUMPRODUCT(--(A1:D4=F1)*COLUMN(A1:D4)))

WOW! That worked perfectly. Thanks so much!

 

[alansidman]

You are welcome. Thanks for the feedback.