asked on

# Excel Formula that provides nth instance of text, based on multiple if conditions

Hi, I'm having trouble with a nested if/small/index formula. What I want to achieve: Use the Text/Date/Number from a different tab (Direkt) and only show the first, second, third... nth instance. The if Statement is based on two conditions.

I have two formulas, one to show the first instance (A) (That one works great) and one that is supposed to show the 2nd/nth instance. That one I only get to work with one if statement, but not with a second condition. With two if statements, it returns the very first value, no matter the if statement.

So here are my two questions:

1. How can I fix my formula to make it work with two if statements.

2. Can I simplify the formula somehow?

(A) {=IFERROR(INDEX(Direkt!$R$5:$R$6999;MATCH(1;(Abrechnung!$G$19=Direkt!$O$5:$O$6999)*(Abrechnung!$G$20=Direkt!$Q$5:$Q$6999);0));"")}

(B) {=IFERROR(INDEX(Direkt!$R$5:$R$6999;SMALL(IF((Abrechnung!$G$20=Direkt!$Q$5:$Q$6999)*(Abrechnung!$G$19=Direkt!$O$5:$O$6999)

;ROW(Direkt!$Q$5:$Q$6999)-ROW(Direkt!$Q$5)+1);2));"")}

ASKER

thanks, that helped a lot. One more question: How can add "If Result is 0; then " "? I used your correction, and then tried to add an if Function, but I get an error.l

=IFERROR( IF( INDEX(Direkt!$U$5:$U$6999;SMALL(IF((Abrechnung!$G$20=Direkt!$Q$5:$Q$6999)*(Abrechnung!$G$19=Direkt!$O$5:$O$6999);ROW(Direkt!$Q$5:$Q$6999)-ROW(Direkt!$Q$5)+1);ROWS(Direkt!$I$2:$I2))) =0; ""; INDEX(Direkt!$U$5:$U$6999;SMALL(IF((Abrechnung!$G$20=Direkt!$Q$5:$Q$6999)*(Abrechnung!$G$19=Direkt!$O$5:$O$6999);ROW(Direkt!$Q$5:$Q$6999)-ROW(Direkt!$Q$5)+1);ROWS(Direkt!$I$2:$I2))) ;"")

That would be very helpfull

I hope you aren't using Excel 2007, because I chose the AGGREGATE function for the newly added formulas.

ArrayFormula 2 criteria.xlsx

The attached workbook shows all of the formulas now working on column U. Your formula (corrected) appears in column M.

ArrayFormula 2 criteria.xlsx

=INDEX(OFFSET(L1,MATCH(D4,K:K,0)-1,0,COUNTIF(K:K,D4),1),E4)

In attached file:

OFFSET(L1,MATCH(D4,K:K,0)-1,0,COUNTIF(K:K,D4),1) creates a range using L1 as a reference point (just the top of the column) and goes down column K until it finds the first instance of the first criteria (D4) and uses that as the start point for the range. The height of the range is then set by the COUNTIF for the number of occurrences of the first criteria.

INDEX then takes the value for the occurrence from E4 and returns that row from the OFFSET range.

This does assume that the values are grouped, not necessarily in sequential order. If the entries are not grouped by the first IF criteria there may be a way of using the TEXTJOIN function to create an array from which you extract the relevant entry. Are all results the same number of characters?

Multi condition lookup.xlsx