multi criteria multi result lookup

[ajm]

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

[TABLE="width: 432"]
<colgroup><col width="64" style="width: 48pt;" span="9"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]AG5[/TD]
[TD="width: 64, bgcolor: #FFE699"]52MFD[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AG6[/TD]
[TD="bgcolor: #FFE699"]444B3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]AJ[/TD]
[TD="bgcolor: transparent"]AK[/TD]
[TD="bgcolor: transparent"]AL[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AG7[/TD]
[TD="bgcolor: #FFE699"]52MF[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: #0070C0"]444B1[/TD]
[TD="bgcolor: #00B0F0"]John Done[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: center"]#NUM![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: #0070C0"]444B1[/TD]
[TD="bgcolor: #00B0F0"]Paul Book[/TD]
[TD="bgcolor: transparent"]A/L[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: #FFE699"]52MFD[/TD]
[TD="bgcolor: #00B0F0"]Sam Johnson[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: #0070C0"]444B1[/TD]
[TD="bgcolor: #00B0F0"]Andrew Bloke[/TD]
[TD="bgcolor: transparent"]A/L[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: #FFE699"]52MF[/TD]
[TD="bgcolor: #00B0F0"]Eric Fan[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: #0070C0"]444B2[/TD]
[TD="bgcolor: #00B0F0"]Paul Peters[/TD]
[TD="bgcolor: transparent"]8.50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: #0070C0"]444B3[/TD]
[TD="bgcolor: #00B0F0"]John Sands[/TD]
[TD="bgcolor: #FFFFCC"]11.50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: #0070C0"]52MFD[/TD]
[TD="bgcolor: #00B0F0"]Guy Noob[/TD]
[TD="bgcolor: transparent"]9.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: #0070C0"]444B1[/TD]
[TD="bgcolor: #00B0F0"]Old Mate[/TD]
[TD="bgcolor: transparent"]10.00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I want to list each name for the three codes at left. what am I doing wrong?

 

[JoeMo]

Copy AM7 down.

Excel Workbook
	AG	AH	AI	AJ	AK	AL	AM
5	52MFD
6	444B3
7	52MF		7	444B1	John Done	10	Sam Johnson
8			8	444B1	Paul Book	A/L	Eric Fan
9			9	52MFD	Sam Johnson	9	John Sands
10			10	444B1	Andrew Bloke	A/L	Guy Noob
11			11	52MF	Eric Fan	10
12			12	444B2	Paul Peters	8.5
13			13	444B3	John Sands	11.5
14			14	52MFD	Guy Noob	9
15			15	444B1	Old Mate	10
Sheet1

 

[ajm]

so, I had the rows at the end wrong and my countif around the wrong way. why in these formulas does the countif layout differ to the normal coumtif? In this context, the criteria is placed before the range.

the fix above works perfectly; thank you. can it be further tweaked to list only those who did not have hours? for example, say eric fan and john sands had no hours this week. how would I adjust the formula to return only those two names?

I have just tried to add in another countif expression looking for null (blank) hours but it seems to skip the 4th argument, any ideas?

 

[JoeMo]

so, I had the rows at the end wrong and my countif around the wrong way. why in these formulas does the countif layout differ to the normal coumtif? In this context, the criteria is placed before the range.

the fix above works perfectly; thank you. can it be further tweaked to list only those who did not have hours? for example, say eric fan and john sands had no hours this week. how would I adjust the formula to return only those two names?

I have just tried to add in another countif expression looking for null (blank) hours but it seems to skip the 4th argument, any ideas?

You are welcome - thanks for the reply. Just FYI, your formula also had the Countif's requiring an AND condition (Countif1*Countif2*Countif3) whereas it should be an OR condition (Countif1+Countif2+Countif3).

As for your new question, assuming that "A/L" in col AL indicates no hours this week, the formula can be adjusted as below.

Excel Workbook
	AG	AH	AI	AJ	AK	AL	AM
5	52MFD
6	444B3
7	52MF		7	444B1	John Done	10	Eric Fan
8			8	444B1	Paul Book	A/L	John Sands
9			9	52MFD	Sam Johnson	9
10			10	444B1	Andrew Bloke	A/L
11			11	52MF	Eric Fan	A/L
12			12	444B2	Paul Peters	8.5
13			13	444B3	John Sands	A/L
14			14	52MFD	Guy Noob	9
15			15	444B1	Old Mate	10
Sheet1

 

[ajm]

thanks very much for your help Joe.

 

[JoeMo]

thanks very much for your help Joe.

You are welcome - thanks for the reply.