Count & Lookup Together with Multiple Range

[SAXON10]

Hi,

I am trying to count based on the lookup multiple ranges.

Example;

In data sheet code "1111" contain count 5 and result is "WIN" against "ENG" . In Sheet-2 code "1111", I have 11 row, formula fill the result first

5 row "WIN" and rest of the row is blanks.

File attached for your reference.

Any help much appreciate

https://www.dropbox.com/s/zl1k8zj8bke9ach/count.xlsx?dl=0


https://www.dropbox.com/s/ugt8v6d2eaxqepc/Capture2.PNG?dl=0



[TABLE="width: 256"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 4"]DATA[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]TEAM[/TD]
[TD]COUNT[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]5[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD]4[/TD]
[TD]NR[/TD]
[/TR]
[TR]
[TD]1116[/TD]
[TD]SA[/TD]
[TD]3[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]6666[/TD]
[TD]PAK[/TD]
[TD]6[/TD]
[TD]LOSS[/TD]
[/TR]
[TR]
[TD]4856[/TD]
[TD]IND[/TD]
[TD]1[/TD]
[TD]DRS[/TD]
[/TR]
[TR]
[TD]2848[/TD]
[TD]WI[/TD]
[TD]1[/TD]
[TD]DRS+R[/TD]
[/TR]
[TR]
[TD]2111[/TD]
[TD]NZ[/TD]
[TD]1[/TD]
[TD]DRS

[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 192"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 3"]DESIRED RESULT[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]TEAM[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD]NR[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD]NR[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD]NR[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD]NR[/TD]
[/TR]
[TR]
[TD]1115[/TD]
[TD]BAN[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1111[/TD]
[TD]ENG[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1116[/TD]
[TD]SA[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1116[/TD]
[TD]SA[/TD]
[TD]WIN[/TD]
[/TR]
[TR]
[TD]1116[/TD]
[TD]SA[/TD]
[TD]WIN[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

Formula in Sheet2 C3 copied down
=IF(COUNTIFS(A$3:A3,A3,B$3:B3,B3)<=SUMIFS(Data!C:C,Data!A:A,A3,Data!B:B,B3),VLOOKUP(A3,Data!$A:$D,4,0),"")

Hope this helps

M.