Avatar of Max Posch
Max Posch
Flag 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));"")}

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

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
Rob Henson

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.
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
byundt



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

ArrayFormula 2 criteria.xlsx

byundt

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