Multiple criteria for array index match

[mwhite80]

I have created a array function using index match based on the age of my data. I have gotten the first criteria to work but can not seem to get the second to work. I have created dynamic ranges for my case numbers, Age and Employees

Working: =SMALL(IF($E$6="",IF($E$5=CREIndex,AZindexREV)),$A$10:$A$2800)

I need to be able to default $E$6 to blank but then enter case numbers. The EEIndex listed above is a dropdown that is working great and filteres my results. The problem is when I enter a case number into $E$6. It errors out.

How do I build this out? Is it an if(or? I have been trying to complete this for several days and need the second piece to work.

 

[DILIPandey]

Hi mwhite80,

You need small of AZindexREV or column A based on if e6 is blank or NOT.
I believe you need to remove one bracket after AZindexREV.

There could be other posssiblites as well. Not able to help much without seeing the data.

Regards,
DILIPandey

 

[williamgowtama]

Because in your code, you have to add a condition for the if value is false (in this case E6.value <> "") i will mark the correction that you need to do by "your condition"

=SMALL(IF($E$6="",IF($E$5=CREIndex,AZindexREV),"your condition"),$A$10:$A$2800)

The Error is Caused by the If condition of the E6 which states that if E6 = 0 then do IF($E$5=CREIndex,AZindexREV), but if E6 <> 0 (which in case you said that u change the value, is blank). My suggestion would be:

=SMALL(IF($E$6="",IF($E$5=CREIndex,AZindexREV,""),IF($E$5=CREIndex,AZindexREV,"")),$A$10:$A$2800)

which wouldn't cause you any error, or

=SMALL(IF($E$5=CREIndex,AZindexREV,""),$A$10:$A$2800)

Because the E6.value not only 0, but you will change it to any number.

Hope it helps,

William