My formula with 4 conditions doesn't return correct value

[noeste]

Hello,

I have learnt a lot about excel here, but I'm blocked with a formula which doesn't return the correct value whatever combinations I have tried.

In the file below (in french sorry), in the cell (D19) I would like to return the customer name corresponding the expiring contract date (E19) but with 3 others conditions (C1,B7,D7).
The data are located in the tab BDD_Client, I can have several customer for a same date.

My formula
=IFERROR(INDEX(BDD_Client!$D$3:$D$9999;SMALL(IF(BDD_Client!A:A=$C$1;IF(BDD_Client!B:B=$D$7;IF(BDD_Client!C:C=$B$7;IF(BDD_Client!K:K=E19;ROW(BDD_Client!$D$3:$D$9999)-ROW(BDD_Client!$D$3)*0))));ROWS(E$19:N19)));"")





Thank you for your help.

noeste

 

[bosco_yip]

Try..............

D19, array formula "Copy" / "Paste" to G19, J19 and all copied down :

=IFERROR(INDEX(BDD_Client!$D$3:$D$9999,SMALL(IF(BDD_Client!$A$3:$A$9999=$C$1,IF(BDD_Client!$B$3:$B$9999=$D$7,IF(BDD_Client!$C$3:$C$9999=$B$7,IF(BDD_Client!$K$3:$K$9999=E19,ROW(BDD_Client!$D$3:$D$9999)-ROW(BDD_Client!$D$2))))),COUNTIF(E$19:E19,E19))),"")

p.s Array formula to be confirmed with CTRL+SHIFT+Enter instead of just Enter.

Regards
Bosco

 

[Luke M]

The problem is that you sort the dates, which is nice, but then they're used as a criteria for the names. So, let's look at cells E19:E30. By the time you get to the first instance of 6/30/2019, your ROWS counter is already at 4, so it's skipping the first 3 names.

I'd suggest using a hidden helper formula (in col C perhaps?) that figures out the row numbers you want. Then you can do a lightweight index. Attached is my recommendation with formulas simplified.

 

[noeste]

Thank you bosco_yip and Luke M for yours ninja replies

@bosco_yip your formula meets my needs, I have understood my error.

@Luke M I understand your approach to simplify the formula I will try to reapply it in my file to see if is more efficient