2 INDEX-MATCH with IF

[reckless2k2]

I have two INDEX-MATCH formulas that I'm trying to chain together with an IF statement that I was looking for help.

INDEX-MATCH 1:
{=-IFERROR(INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=-J2)*(filtered!E:E=E2),0),10),0)}

INDEX-MATCH 2:
{=IFERROR(INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=J3)*(filtered!E:E=E2)*(filtered!B:B=B2),0),10),0)}

IF Statement beginning....

=IF(B2="TEXT", (INDEX-MATCH 1), (INDEX-MATCH2)

Thanks for any help!

 

[Aladin Akyurek]

You are fetching a result from column J of filtered, right? Why are you testing whether this column also equals -J2 or J3?

 

[reckless2k2]

Sorry.....that was just a typo. It should all be row 2. Depending on the B2 "TEXT", I need different results. That's why I have two INDEX-MATCH formulas that I'm trying to put together in one IF statement.

 

[Aladin Akyurek]

Sorry.....that was just a typo. It should all be row 2. Depending on the B2 "TEXT", I need different results. That's why I have two INDEX-MATCH formulas that I'm trying to put together in one IF statement.

From which range (column) do we fetch a result if not column J?

 

[reckless2k2]

From which range (column) do we fetch a result if not column J?

I was in Leiden a few months ago for work. NL is beautiful country and very nice people.

I tried to put the formula together in this way but it did not work...

{=IF(B2="TEXT",(-INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=-J2)*(filtered!E:E=E2),0),10),(INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=J2)*(filtered!E:E=E2)*(filtered!B:B=B2),0),10))))}

That was after I removed the IFERROR in front but it still was not working.

 

[Aladin Akyurek]

I was in Leiden a few months ago for work. NL is beautiful country and very nice people.

Kind of you to say so.

I tried to put the formula together in this way but it did not work...

{=IF(B2="TEXT",(-INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=-J2)*(filtered!E:E=E2),0),10),(INDEX(filtered!A:Y,MATCH(1,(filtered!W:W=W2)*(filtered!J:J=J2)*(filtered!E:E=E2)*(filtered!B:B=B2),0),10))))}

That was after I removed the IFERROR in front but it still was not working.

Try to avoid whole column references in this type of formulas though for reasons of efficiency.

Control+shift+enter, not just enter:

=INDEX(filtered!$J$2:$J$1000,MATCH(1,IF(filtered!$W$2:$W$1000=W2,IF(filtered!$J$2:$J$1000=-J2,IF(filtered!$E$2:$E$1000=E2,IF(filtered !$B$2:$B$1000=IF(B2="TEXT",B2,filtered !$B$2:$B$1000),1)))),0))*IF(B2="TEXT",1,-1)

Does this work for you?