Index Match multiple criteria with IF and OR

Ellie456

New Member
Joined
Sep 20, 2015
Messages
27
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
Sep 20, 2015
Messages
27
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
 
Last edited:

Ellie456

New Member
Joined
Sep 20, 2015
Messages
27
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)

 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top