Find position in matrix (not array)

**Coaster** · 03-16-2010, 11:59 PM

Hi,

How can I find the position of an item in a matrix? The function MATCH only works with arrays, not matrices... I want to know both line and column relative position

Thanks

**DonkeyOte** · 03-17-2010, 03:22 AM

MATCH will only work with Vectors.

Returning the address in one cell is a relatively expensive process, generally it's best to split into two to reduce workload:

A2: =MIN(IF(B1:D100=A1;COLUMN(B1:D1)+(ROW(B1:B100)/1000000)))
confirmed with CTRL + SHIFT + ENTER

A3: =ADDRESS(MOD(A2,1)*1000000;INT(A2))
confirmed with Enter

The Array will return first match based on Column - ie A50 would be found before B4
If you want ROW to precede COLUMN (ie find B4 before A50) then switch COLUMN & ROW in the Array and modify subsequent ADDRESS function accordingly.

**Coaster** · 03-17-2010, 10:13 PM

tks for the explanation!

however, I need more help. I got that to work but now I don't know what to do with it to do what I want to do...I'll attach an .xls file so you can understand what I want.

Thanks

**Teethless mama** · 03-17-2010, 10:55 PM

=max(index((c5:j11=c16)*b5:b11,))&b4&max(index((c5:j11=c16)*c4:j4,))

**Coaster** · 03-18-2010, 01:27 AM

worked perfectly! thanks

**AdLoki** · 11-14-2011, 01:26 PM

Originally Posted by Teethless mama

=max(index((c5:j11=c16)*b5:b11,))&b4&max(index((c5:j11=c16)*c4:j4,))

Hi this is a really interesting formula, not one I've come across before -- can anyone give a little bit more explanation about how the index function is working here? Many thanks.