Index Match multiple criteria with IF and OR

[Ellie456]

Hi all,

I have tried to expand my formula to bring back the result I need and I'm not getting very far. This is my formula so far


IF(OR(AY09="4A",AY09="4B"),<wbr>INDEX('[Matrix.xlsx]ICS Accounts'!$C:$C,MATCH("TOTAL-<wbr>04.09-A",'[Matrix.xlsx]Accounts'!$E:$E,0),MATCH(LEFT(<wbr>AW09,10),'[Matrix.xlsx]Accounts'!$C:$C,0)),AW09)


This part of the formula

MATCH(LEFT(​

<wbr style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: normal; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">

AW09,10),'[Matrix.xlsx]Accounts'!$C:$C,0))​

I am trying to say search column C within the matrix and match the first 10 characters of column C which will then match cell AW09. With all this match criteria within my formula I should get the right result but it's not working.

Can anyone see what's wrong with my formula?

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IF(OR(AY9="4A",AY9="4B"),INDEX('[Matrix.xlsx]Accounts'!$C:$C,MATCH(1,IF('[Matrix.xlsx]Accounts'!$E:$E="TOTAL-04.09-A",IF('[Matrix.xlsx]Accounts'!$C:$C=LEFT(AW9,10),1)),0)),AW9)

Does this work for you?

 

[Ellie456]

Hi Aladin,

thanks for responding.

It has only partially worked again, it has returned a result for all items that don't match 4A and 4B and I did get this problem earlier as well, so for the NON 4A and 4B calculations it has returned a single value as expected of B130000020.

The items that match 4A and 4B should return something like this B130000020.X1000000030 and this is where the left function should match AY10 to the first 10 characters of column C but I am getting N/A's.

Any ideas on how I can tweak your formula?

I also used

Control+shift+enter​

 

[Ellie456]

IF('[Matrix.xlsx]Accounts'!$C:$C=LEFT(AW9,10),1)),0)),AW9)​

I need the above part of the formula to be the other way around if possible. I want it to look up the first 10 characters in COLUMN C of the matrix and match it to cell AW9 which has a value of B130000020 so it should return from the matrix column C the following

B130000020.X1000000030

My matrix has the following types of values and I want to match cell AW9 which contains B13000020 to the first 10 characters of column C below

COLUMN C COLUMN E

​

C130000020 TOTAL-01.06.A
C140000030 TOTAL-01.05.A

B130000020.X1000000030 TOTAL-04.09-A
B130000020.X1000000040 TOTAL-04.09-X
B130000020.X1000000050 TOTAL-04.09-Z

So I think the left function should possibly be something like this

IF(LEFT('[Matrix.xlsx]Accounts'!$C:$C,10=AW9),1)),0)),AW9)​

​

 

[Aladin Akyurek]

IF('[Matrix.xlsx]Accounts'!$C:$C=LEFT(AW9,10),1)),0)),AW9)​

I need the above part of the formula to be the other way around if possible. I want it to look up the first 10 characters in COLUMN C of the matrix and match it to cell AW9 which has a value of B130000020 so it should return from the matrix column C the following

B130000020.X1000000030

My matrix has the following types of values and I want to match cell AW9 which contains B13000020 to the first 10 characters of column C below

COLUMN C COLUMN E

​

C130000020 TOTAL-01.06.A
C140000030 TOTAL-01.05.A

B130000020.X1000000030 TOTAL-04.09-A
B130000020.X1000000040 TOTAL-04.09-X
B130000020.X1000000050 TOTAL-04.09-Z

So I think the left function should possibly be something like this

IF(LEFT('[Matrix.xlsx]Accounts'!$C:$C,10=AW9),1)),0)),AW9)​

​

What do we have in AW9?

What must the range in C match if anything?

What must the range in E match if anything?

What is the result that must obtain?

What is the role of AY9?