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

DATA
CODE	TEAM	COUNT	RESULT
1111	ENG	5	WIN
1115	BAN	4	NR
1116	SA	3	WIN
6666	PAK	6	LOSS
4856	IND	1	DRS
2848	WI	1	DRS+R
2111	NZ	1	DRS

<colgroup><col span="4"></colgroup><tbody>
</tbody>

DESIRED RESULT
CODE	TEAM	RESULT
1111	ENG	WIN
1111	ENG	WIN
1111	ENG	WIN
1111	ENG	WIN
1115	BAN	NR
1115	BAN	NR
1115	BAN	NR
1115	BAN	NR
1115	BAN
1111	ENG	WIN
1111	ENG
1111	ENG
1111	ENG
1111	ENG
1111	ENG
1111	ENG
1116	SA	WIN
1116	SA	WIN
1116	SA	WIN

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[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.