Index match if and

[Middleoftheroad]

Hi,

I have been puzzling over an Index match and formula for a few weeks now and was hoping someone could give me a nudge in the right direction.

I created the below formula which works on one spreadsheet (a). When I try to extend the formula to look at 2 columns (different value in each) I can't get it to work. See formula (b) below is what I have come up with but it doesn't work.

Any help would be really really appreciated!

Working formula (a) {=INDEX(INDIRECT("'"&$C$2&"'!D$2:D$908"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$C$908")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$C$2:$C$908"))-1),ROWS($C$32:C32)))}

Extended not working formula (b) INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(AND(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000"))-1),ROWS($C$32:C32))))

I can't get formula b to move to array because of errors

Thanks

 

[sheetspread]

Instead of and() add another if()

 

[Middleoftheroad]

Instead of and() add another if()

Thank you for responding Sheetspread. I updated as advised with the below.

INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000"))-1),ROWS($C$32:C32))))

This gives the too few arguments for this function error box. Have I got right what you advised? Thanks

 

[sheetspread]

This any better:

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))


?

 

[Middleoftheroad]

This any better:

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))


?

Thank you Sheetspread! It accepts the formula now, really appreciate the help!

It does lead to another question though. When I turn the formula to an array it either pulls a #num or a #value depending on it I put in 2017 or 2017/2018 in cell C3. Any hints on what I am clearly missing?

Thank you

 

[sheetspread]

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))

Did you intend to search 2 dimensions or just a single column there?

 

[markmzz]

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))

Did you intend to search 2 dimensions or just a single column there?

Hi Sheetspread,

Maybe the problem is in red:

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,
IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,
IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000"))-1)),ROWS($C$32:C32)))
​

Middleoftheroad,

Try this small modification in Sheetspread's formula too:

=INDEX(INDIRECT("'"&$C$2&"'!G2:G3000"),SMALL(IF(MMULT(--(INDIRECT("'"&$C$2&"'!C2:F3000")=$C$3),{1;1;1;1}),
IF(INDIRECT("'"&$C$2&"'!F2:F3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!F2:F3000"))-1)),ROWS((C$32:C32)))

Markmzz

 

[Middleoftheroad]

Hi Sheetspread,

Maybe the problem is in red:

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,
IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")))-1),ROWS($C$32:C32)))

=INDEX(INDIRECT("'"&$C$2&"'!G$2:G$3000"),SMALL(IF(INDIRECT("'"&$C$2&"'!$C$2:$F$3000")=$C$3,
IF(INDIRECT("'"&$C$2&"'!$F$2:$F$3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!$F$2:$F$3000"))-1)),ROWS($C$32:C32)))
​

Middleoftheroad,

Try this small modification in Sheetspread's formula too:

=INDEX(INDIRECT("'"&$C$2&"'!G2:G3000"),SMALL(IF(MMULT(--(INDIRECT("'"&$C$2&"'!C2:F3000")=$C$3),{1;1;1;1}),
IF(INDIRECT("'"&$C$2&"'!F2:F3000")=$B$3,ROW(INDIRECT("'"&$C$2&"'!F2:F3000"))-1)),ROWS((C$32:C32)))

Markmzz

Thank you both for all the help with this! Markmzz your formula worked perfectly. I had no idea about the MMULT function (every day is a school day!). Having thought I was ok at Excel I have been on a big learning curve over the last few months and really appreciate the help!

 

[markmzz]

Thank you both for all the help with this! Markmzz your formula worked perfectly. I had no idea about the MMULT function (every day is a school day!). Having thought I was ok at Excel I have been on a big learning curve over the last few months and really appreciate the help!

Hi!

First, you're welcome and thanks for the return.

Second, the ultimate goal of MMULT function is to find out if at least one value for each row in the range C2:F3000 has a match with the value of cell C3 (J3 in my example).

=INDEX($G$2:$G$30,SMALL(IF(MMULT(--($C$2:$F$3000=$J$3),{1;1;1;1}),
IF($F$2:$F$3000=$I$3,ROW($F$2:$F$3000)-1)),ROWS(K$3:K3)))

In the sheet below in the range H6:K9 we have the result of the first argument of the MMULT function for the rows 6, 7, 8 and 9. In the range L6:L9 we have the result of the MMULT function for the rows 6, 7, 8 and 9.


[TABLE="class: grid, width: 515"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1231[/TD]
[TD]2070[/TD]
[TD]3538[/TD]
[TD]4708[/TD]
[TD]7545[/TD]
[TD][/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1990[/TD]
[TD]2215[/TD]
[TD]3394[/TD]
[TD]4655[/TD]
[TD]7330[/TD]
[TD][/TD]
[TD]4293[/TD]
[TD]2612[/TD]
[TD]7171[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]1196[/TD]
[TD]2707[/TD]
[TD]3257[/TD]
[TD]4162[/TD]
[TD]5298[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7544[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1991[/TD]
[TD]2775[/TD]
[TD]3705[/TD]
[TD]4254[/TD]
[TD]7462[/TD]
[TD]C
[/TD]
[TD]D[/TD]
[TD]E
[/TD]
[TD]F[/TD]
[TD="align: right"]2612[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]1257[/TD]
[TD]2612[/TD]
[TD]3831[/TD]
[TD]4293[/TD]
[TD]7171[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]1029[/TD]
[TD]2752[/TD]
[TD]3745[/TD]
[TD]4617[/TD]
[TD]7886[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]1863[/TD]
[TD]2734[/TD]
[TD]3731[/TD]
[TD]4339[/TD]
[TD]7652[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]1537[/TD]
[TD]2259[/TD]
[TD]2612[/TD]
[TD]4293[/TD]
[TD]7544[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]1102[/TD]
[TD]2624[/TD]
[TD]3790[/TD]
[TD]4590[/TD]
[TD]7134[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD="align: right"]2612[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]1625[/TD]
[TD]2829[/TD]
[TD]3154[/TD]
[TD]4502[/TD]
[TD]7232[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]1458[/TD]
[TD]2287[/TD]
[TD]3433[/TD]
[TD]4146[/TD]
[TD]5513[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]1789[/TD]
[TD]2106[/TD]
[TD]3842[/TD]
[TD]4083[/TD]
[TD]5764[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]1644[/TD]
[TD]2599[/TD]
[TD]3001[/TD]
[TD]4237[/TD]
[TD]6912[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]1655[/TD]
[TD]2633[/TD]
[TD]3655[/TD]
[TD]4067[/TD]
[TD]5882[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]

I hope that this helps.

Markmzz