trying to adapt https://www.get-digital-help.com/match-two-criteria-and-return-multiple-rows-in-excel/ to work for me. my version of the formula is:
=INDEX($AK$7:$AK$15, SMALL(IF(COUNTIF($AG$5,$AJ$7:$AJ$15)*COUNTIF($AG$6,$AJ$7:$AJ$15)*COUNTIF($AG$7,$AJ$7:$AJ$15), ROW($AJ$7:$AL$15)-MIN(ROW($AJ$7:$AL$15))+1), ROW(A7)), COLUMN(A7))
<colgroup><col width="64" style="width: 48pt;" span="9">
<tbody>
</tbody>I want to list each name for the three codes at left. what am I doing wrong?
=INDEX($AK$7:$AK$15, SMALL(IF(COUNTIF($AG$5,$AJ$7:$AJ$15)*COUNTIF($AG$6,$AJ$7:$AJ$15)*COUNTIF($AG$7,$AJ$7:$AJ$15), ROW($AJ$7:$AL$15)-MIN(ROW($AJ$7:$AL$15))+1), ROW(A7)), COLUMN(A7))
AG5 | 52MFD | |||||||
AG6 | 444B3 | AJ | AK | AL | ||||
AG7 | 52MF | 7 | 444B1 | John Done | 10.00 | #NUM! | ||
8 | 444B1 | Paul Book | A/L | |||||
9 | 52MFD | Sam Johnson | 9.00 | |||||
10 | 444B1 | Andrew Bloke | A/L | |||||
11 | 52MF | Eric Fan | 10.00 | |||||
12 | 444B2 | Paul Peters | 8.50 | |||||
13 | 444B3 | John Sands | 11.50 | |||||
14 | 52MFD | Guy Noob | 9.00 | |||||
15 | 444B1 | Old Mate | 10.00 |