Searching a whole Row in the table to return Row number or Corresponding cell.

Hi Everyone,

The situation is very clear from the picture. The aim is to search F2:H2 (Name, Team, Jersy No) collectively in the A1:C7 table and return corresponding Goals.I have used the following formula:-

=INDEX(D1:D7,MATCH(3,MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0)),0))

The formula is An Array formula. So use CTRL + SHIFT + ENTER to confirm it.

I will start by explaining the following part:-

MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0))

Here is a small Explanation of Mmult function. Mmult function multiplies two matrices. The important thing is the no. of columns of first Matrix must be equal to no. of rows of second Matrix.For simplicity:- N x 2 matrix can multiply with 2 x N matrix as No. of Columns (2) in first matrix are equal to No. of Rows (2) in second matrix and the result would be N X N matrix.

Transpose function just transposes the matrics or array. For eg. 3 X 2 Matrix becomes 2 X 3 Matrix.

Now starting with first matrix ie. --(A1:C7=F2:H2). The below is how this part performs:-

	A1:C7			=		F2:H2
Name	Team	Jersy No
Costa	Chelsea	21
Ronaldo	Real	7
Messi	Barca	6		=	Ronaldo	Real	7
Saurez	Barca	13
Neymar	Barca	10
Remos	Real	14

what actually excel does is to compare each column in A1:C7 table with corresponding column in F2:H2. So above equation can be further extended like this:-

Name			Team			Jersy No
Costa			Chelsea			21
Ronaldo			Real			7
Messi	= Ronaldo		Barca	= Real		6	= 7
Saurez			Barca			13
Neymar			Barca			10
Remos			Real			14

It will return an array or table of TRUE and FALSE which would be further converted into 1s and 0s using -- signs. The resultant array would look like this.

0			0			0
0			0			0
1			1			1
0			0			0
0			0			0
0			0			0
0			1			0

That was the making of our first 7 X 3 matrix. But the actual problem here is, this 7 X 3 matrix can never be used to get the row no. of Match Row (3rd row). So that's where Mmult comes into picture. By using Mmult, we can combine all these columns to make it 7 X 1 matrix or array that is perfect for further conclusions and processing. Actually, using Mmult, we add all corresponding elements of all three columns of above matrix. Following is how its done:-

0	0	0							0	(0x1 + 0x1 + 0x1)
0	0	0				1			0	(0x1 + 0x1 + 0x1)
1	1	1	7 X 3 multiplied by 3 X 1			1	to get 7 X 1 matrix ie.		3	(1x1 + 1x1 + 1x1)
0	0	0				1			0	(0x1 + 0x1 + 0x1)
0	0	0							0	(0x1 + 0x1 + 0x1)
0	0	0							0	(0x1 + 0x1 + 0x1)
0	1	0							1	(0x1 + 1x1 + 0x1)

The 3 X 1 matrix is produced by using Columns of 7 X 3 matrix. The COLUMN(A1:C1)^1 return a 1 X 3 array ie. {1,1,1} then using TRANSPOSE function will return a 3 X 1 matrix ( {1;1;1} which is required.

So That’s how MMULT(--(A1:C7=F2:H2),TRANSPOSE(COLUMN(A1:C1)^0)) reduces to {0;0;3;0;0;0;1}

Now with some common sense and keeping matrix multiplication in mind, we can easily conclude that if a row in the A1:C7 table exactly matches with F2:H2 then that row will tend to return 3 in the array returned by Array multiplication. As we can notice, third element of {0;0;3;0;0;0;1} is 3.

Now using Match Function with lookup value 3 would return the row number of the matched row. That will further used by Index Function to return the 3rd cell in D1:D7 Range ie 18.

That was all what I think this construction performs. However if any one among readers have any query or suggestion then Do comment.

Regards,

Vikas Gautam

Check the attached file:-

Searching a whole row.xlsx