more complicated index() / match() function?

**theillknight** · 02-11-2005, 03:59 PM

Pretend I have an array like this:

1 2 7 3
5 3 9 4
4 10 6 8

How can I use Excel functions to find the row/col of a number of my choosing? For simplicity's sake, assume all values in the array are unique.

For example, in cell C1 I type: 2. I want C2 to tell me it's in row 1, column 2. I've tried using Match but I think you can only select a single row/column at a time for that.

**Domenic** · 02-11-2005, 08:46 PM

Try the following...

For the row number:

=SUMPRODUCT((A1:D3=F1)*(ROW(A1:D3)))

For the column number:

=SUMPRODUCT((A1:D3=F1)*(COLUMN(A1:D3)))

For both row and column number:

="Row "&SUMPRODUCT((A1:D3=F1)*(ROW(A1:D3)))&", Column "&SUMPRODUCT((A1:D3=F1)*(COLUMN(A1:D3)))

...where F1 contains the number of interest.

Hope this helps!

Originally Posted by theillknight

Pretend I have an array like this:

1 2 7 3
5 3 9 4
4 10 6 8

How can I use Excel functions to find the row/col of a number of my choosing? For simplicity's sake, assume all values in the array are unique.

For example, in cell C1 I type: 2. I want C2 to tell me it's in row 1, column 2. I've tried using Match but I think you can only select a single row/column at a time for that.

**olasa** · 02-12-2005, 09:54 PM

Here are 2 alternatives:

A. Relative position:
Row: =SUMPRODUCT((C1=A4:C6)*ROW(OFFSET(A1,0,0,ROWS(A4:C6),1)))
Col: =SUMPRODUCT((C1=A4:C6)*COLUMN(OFFSET(A1,0,0,1,COLUMNS(A4:C6))))
Test: =INDEX(A4:C6,Row,Col)

B. Absolute position:
See Domenic's solution

Ola Sandstrom

Note:
I've excluded all $ for better readability