A | B | C | D | E | F | |
1 | HA Test 1 | HA Test 2 | HA Test 3 | Criteria | ||
2 | Jim | 10/09/17 (C) | 10/09/17 (A) | 10/09/17 (C) | HA Test 1 | |
3 | Jane | 10/09/17 (A) | 10/09/17 (C) | 10/09/17 (A) | 10/09/17 (A) | |
4 | Roger | 10/09/17 (A) | 10/09/17 (C) | 10/09/17 (C) | ||
5 | Tim | 10/09/17 (A) | 10/09/17 (A) | 10/09/17 (C) |
<tbody>
</tbody>
Hi All,
I have a problem that i cant resolve. (i have looked on this site but unable to find)
I am trying to list out names relating to criteria which = "HA Test 1" & "date" & "(A)", i can find 1 name using: =INDEX($A$2:$A$5,MATCH($F$3,INDEX($B$2:$D$5,0,MATCH($F$2,$B$1:$D$1,0)),0),0) which will = Jane but i also want the formula to show the next 2 (Roger & Tim). Therefore when changing the criteria to "HA Test 2" it will return Jim & Tim.
I have tried using: =IF(ROWS($H$13:H13)>$J$13,"",INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$F$3,ROW($B$2:$D$5)-ROW(B$2:D$2)+1),ROWS($H$13:H13)))) with the use of a helper cell = $J$13 (this would only relate to the number of names that meet that criteria).
Any help would be great,
Regards
Rudi