Trouble with index/match in a matrix

[Reboshua]

Hi, I keep getting an #ref error when trying to populate the left matrix with data from the right table (located on another worksheet).

I am using the following in b2:=index(j:j,match(b1,h:h,0),match(a2,i:i,0))

and that yields the #ref error.

Any advice?



Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sally[/TD]
[TD]Bobby[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]

20180329​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD="bgcolor: #D9E1F2"]

20180329​

[/TD]
[TD]

91​

[/TD]
[/TR]
[TR]
[TD]

20180330​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD]

20180330​

[/TD]
[TD]

87​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]

20180331​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD="bgcolor: #D9E1F2"]

20180331​

[/TD]
[TD]

45​

[/TD]
[/TR]
[TR]
[TD]

20180401​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD]

20180401​

[/TD]
[TD]

89​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]

20180402​

[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sally[/TD]
[TD="bgcolor: #D9E1F2"]

20180402​

[/TD]
[TD]

87​

[/TD]
[/TR]
[TR]
[TD]

20180403​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bobby[/TD]
[TD]

20180330​

[/TD]
[TD]

77​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bobby[/TD]
[TD="bgcolor: #D9E1F2"]

20180331​

[/TD]
[TD]

72​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD="bgcolor: #D9E1F2"]

20180329​

[/TD]
[TD]

100​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD="bgcolor: #D9E1F2"]

20180331​

[/TD]
[TD]

85​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD]

20180403​

[/TD]
[TD]

92​

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD]

20180401​

[/TD]
[TD]

81​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet11[/TD]
[/TR]
</tbody>[/TABLE]

 

[luke_forster]

To my knowledge the index function needs both a row number and a column number. From the looks of what you are doing you are trying to give it 2 row numbers.

Instead I would suggest adding a formula in column K that is = H2 & I2.

Then you can use the following formula in cell B2.

=INDEX($J$2:$J$12, MATCH(B$1&$A2, $K$2:$K$12, 0), 1)

 

[Reboshua]

Thank you, this worked. I used concatenated helper columns all the time, but didn't know you could combine like that.

Cheers!

 

[luke_forster]

Reboshua,

Glad I was able to help!

-Luke