Search a multiple dimensional array

[darknation144]

So I'm really stuck on this one. So I have a multi dimentional array of numbers and they are random I need to be able to search for a given number where ever it is. It could be in any row or column. I know I need to use an array formula but can't quite get it to work.

In this example I am trying to get index() to reference 200.

In the end I will also need to return all the values below the 200 but that is easy if I crack the referencing as you can just add +1 to the row.

I've managed to get it working if I know which column the value will be in but it could be in any column or row which is when the problem comes in.

Matrix.xls

[Paul]

Hi Darknation,

If you want to get the address where I4 is found in your table, you could use:

=ADDRESS(SUMPRODUCT((A1:D6=I4)*(ROW(A1:D6))),SUMPRODUCT((A1:D6=I4)*(COLUMN(A1:D6))))

If you just want the row or column you can separate those out to:

Row: SUMPRODUCT((A1:D6=I4)*(ROW(A1:D6)))

Column: SUMPRODUCT((A1:D6=I4)*(COLUMN(A1:D6)))

[darknation144]

Deleted Post