Link to home
Start Free TrialLog in
Avatar of Max Posch
Max PoschFlag for Germany

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 CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can achieve that using the OFFSET function to create the range and an INDEX function to return the nth entry from the range.

=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
With a helper column to count the occurrence of entries, you could create a pivot table and use the filter criteria to show the desired result.
Avatar of Max Posch

ASKER

Hi byundt,

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

User generated image

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

ArrayFormula 2 criteria.xlsx

I missed the change from Direct column R to Direct column U. Your attempted formula was just missing a right parenthesis at the end of the fourth line.

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

ArrayFormula 2 criteria.xlsx