INDEX array formula using multiple criteria, * and INDIRECT references, Can it work?

[whetu]

Hey guys/girls,

I have been working on trying to pull payments from worksheets, that get generated each month, within my workbook. This is the formula I am using to pull the data (date-column1 and payment-column 2) but it can only use a single criteria, $F$3. I am using INDIRECT to reference different worksheets and ROW references that track down a helper column beside Column F.

=IF(ISERROR(INDEX((INDIRECT("'"&$D28&"'!A:F")),SMALL(IF((INDIRECT("'"&$D28&"'!F:F"))=$F$3,ROW((INDIRECT("'"&$D28&"'!F:F")))),ROW(INDIRECT($C28))),2)),"",INDEX((INDIRECT("'"&$D28&"'!A:F")),SMALL(IF((INDIRECT("'"&$D28&"'!F:F"))=$F$3,ROW((INDIRECT("'"&$D28&"'!F:F")))),ROW(INDIRECT($C28))),2))

What I would like to achieve is the above formula operating with more than one criteria.

Instead of only looking for the value in $F$3, I would like it to INDEX values from $F$1, $F$2 OR $F$3.

For example (and this does NOT work):

=IFERROR(
INDEX((INDIRECT("'"&$D28&"'!A:F")),SMALL(IF(

((INDIRECT("'"&$D28&"'!F:F"))=$F$1)*
((INDIRECT("'"&$D28&"'!F:F"))=$F$2)*
((INDIRECT("'"&$D28&"'!F:F"))=$F$3),

ROW((INDIRECT("'"&$D28&"'!F:F"))),ROW(INDIRECT($C28)),""),ROW((INDIRECT("'"&$D28&"'!F:F")))),ROW(INDIRECT($C28))2),"")

As you can see I have also tried to use IFERROR as well..

Thank you in advance for any help.

This is a stripped down version of the workbook.

FEES TRIAL DUMMY.xlsx

Whets

[misrasomendra]

Hi,

See the file, I Had use IFERROR, hoping you must be on 2010 as your version says under your name. Note using so many INDIRECT is slowing down your workbook, can't you think of using PIVOT or VBA to reduce so much calculation load.

Just check the result. I had merged F2 & F3 condition.

[whetu]

Thank you very much!

Worked right out of the box.

I will try to break it down to understand it, but I looks to be an extremely efficient formula.

I will def try to use a pivot table from here...

Thank you again mate.

Whetu

[misrasomendra]

Thanks for the feedback if you problem is solved than mark the thread as SOLVED and if you feel the solution is useful to you than you can thank by clicking the Add reputation star below my comments.

[whetu]

Done and done! Thanks again!

[whetu]

Not sure on the technicalities of the forum rules here but....

The formula that I got in the above help from misrasomendra was great and efficient (your awesome!), however, if there is no value in the referenced cell (F2 or F3) it returns odd (weird) values. This is because it tries to return rows that have no entries.

Is it possible to rejig this formula to allow it to skip entry less rows (as in a blank value for F2 or F3)

Here is the code directly from her/his answer:

=IFERROR(INDEX(INDIRECT($D4&"!A2:A500"),SMALL(IF((INDIRECT($D4&"!F2:F500")=$E$2)+(INDIRECT($D4&"!F2:F500")=$E$3),ROW(INDIRECT($D4&"!F2:F500"))-2+1),COUNTIF($D$4:$D4,$D4))),"")

Here is the spreadsheet:

353354d1413915941-index-array-formula-using-multiple-criteria-and-indirect-references-can-it-wo.xlsx

Many thanks again!

Just to add... I did spend some 3 hours on this today with results ranging from breaking excel to nearly set PC on fire... I will need to spend some time after this getting my head around this new formula... for now though I need to get this out by Friday.. lol

[misrasomendra]

Hi,

See the file, hope you are looking for something like this.