Hi All. I am trying to use an Index function with two conditions, but seem to be having trouble with the correct formula. The formula works when I use each condition separately. Probably missing something obvious, but I would appreciate your help. The Excel worksheet is attached for your reference. Thanks.
| Index Formula.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Symbol | Date | Loss | Conditions | Date | Loss | Combined | ||||
| 2 | ABT | 10/12/23 | -37% | ABT | AEMD | ||||||
| 3 | ADGM | 10/04/24 | -74% | Date | AHCO | AIMD | |||||
| 4 | AEMD | 08/05/24 | -100% | 11/13/2023 | AORT | ALUR | |||||
| 5 | AHCO | 11/08/23 | -85% | Loss | ARAY | AMIX | |||||
| 6 | AIMD | 10/31/24 | -100% | -90% | ATEC | AORT | |||||
| 7 | ALUR | 09/09/24 | -95% | AVNS | APYX | ||||||
| 8 | AMIX | 10/25/24 | -95% | AXGN | ARAY | ||||||
| 9 | AORT | 08/15/02 | -97% | AXNX | ATEC | ||||||
| 10 | APYX | 08/05/24 | -94% | BIO | AXDX | ||||||
| 11 | ARAY | 03/25/20 | -96% | AXGN | |||||||
| 12 | ATEC | 02/08/19 | -99% | BBLG | |||||||
| 13 | AVNS | 10/27/23 | -76% | BFLY | |||||||
| 14 | AXDX | 04/18/24 | -100% | BJDX | |||||||
| 15 | AXGN | 11/01/23 | -94% | BMRA | |||||||
| 16 | AXNX | 05/31/23 | -40% | ||||||||
| 17 | BBLG | 09/13/24 | -100% | ||||||||
| 18 | BFLY | 04/25/24 | -98% | ||||||||
| 19 | BIO | 10/30/23 | -69% | ||||||||
| 20 | BJDX | 11/04/24 | -100% | ||||||||
| 21 | BMRA | 08/05/24 | -99% | ||||||||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G21 | G2 | =IFERROR(INDEX($A$2:$A$21, SMALL(IF($B$2:$B$21<$E$4, ROW($A$2:$A$21)-ROW($A$2)+1), ROW(1:1))),"" ) |
| H2:H21 | H2 | =IFERROR(INDEX($A$2:$A$21, SMALL(IF($C$2:$C$21<$E$6, ROW($A$2:$A$21)-ROW($A$2)+1), ROW(1:1))),"" ) |
