i need function for combine table

[dabielcrbyton]

a1	b1	c1	d1
2	2		2
2	4		2
4	3		4
4	8		4
8			8

hi everyone i have the excel :

i have a problem
i sit down to finger it out
but i cannot succeed of solving it

i have numbers in a3:a7
and numbers in b3:b6

i need function for d3:d7 ( only the use of function )
the function need to take every number in b3:b6
and if it find it on a3:a7
it should write it on d3:d7


look at that :
1) the number 3 is on b3:b6 but not on a3:a7
so he is not in d3:d7

2) the number 2 is on b3:b6 and twice on a3:a7
so it is twice on d3:d7

3) their is not duplicate number on b3:b6
but in a3:a7 it can be duplicate number and also 3 time the same number
until 4 time the same number


thanks

 

[Toadstool]

Hi dabielcrbyton,

I'm not sure if I've understood correctly but does this do what you want?

dabielcrbyton.xlsx
	A	B	C	D
1	a1	b1	c1	d1
2
3	2	2		2
4	2	4		2
5	4	3		4
6	4	8		4
7	8			8
8
Sheet1

Cell Formulas
Range	Formula
D3:D8	D3	=IFERROR(INDEX($A$3:$A$9999,AGGREGATE(15,6,ROW($A$3:$A$9999)-ROW($A$2)/((NOT(ISNA(MATCH($A$3:$A$9999,$B$3:$B$9999,0)))*($A$3:$A$9999<>""))),ROW()-ROW($D$2))),"")

 

[dabielcrbyton]

i am sorry
but
it don't do anything
just keep the a`l the d cell blank

 

[Toadstool]

Your profile doesn't say what version you are running but AGGREGATE has been in since Excel 2010.

Try without the IFERROR and see what you get.

dabielcrbyton.xlsx
	A	B	C	D
1	a1	b1	c1	d1
2
3	2	2		2
4	2	4		2
5	4	3		4
6	4	8		4
7	8			8
8				#NUM!
Sheet2

Cell Formulas
Range	Formula
D3:D8	D3	=INDEX($A$3:$A$9999,AGGREGATE(15,6,ROW($A$3:$A$9999)-ROW($A$2)/((NOT(ISNA(MATCH($A$3:$A$9999,$B$3:$B$9999,0)))*($A$3:$A$9999<>""))),ROW()-ROW($D$2)))

 

[Marcelo Branco]

Another formula

Pasta1
	A	B	C	D
1	a1	b1	c1	d1
2
3	2	2		2
4	2	4		2
5	4	3		2
6	5	8		4
7	2			4
8	8			8
9	4
10
Plan3

Cell Formulas
Range	Formula
D3:D9	D3	=IFERROR(INDEX($B$3:$B$6,AGGREGATE(15,6,MATCH(A$3:A$100,B$3:B$6,0),ROWS(D$3:D3))),"")

M.