I can use SUMPRODUCT to check IF a range of values exists in another range; how do I extract them into another list?

[SleightOfHand]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Station 1				Station 1	Station 2	Station A	Station B	Station C
2	Station 2
3	Station 3
4	Station 4
5	Station 5			1
6	Station 6
7	Station 7
8	Station 8
9
10
Sheet1

Cell Formulas
Range	Formula
D5	D5	=IF(SUMPRODUCT(--(A1:A8=E1:I1))>0,1,0)

I've used a formula to determine whether the stations from I1 are matched in in any case with the column A1:A7. But this just tells me that these exist; how, for a longer list, would I be able to tell WHAT the actual values that match would be?

 

[Eric W]

Welcome to the forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Station 1				Station 1	Station 2	Station A	Station B	Station C
2	Station 2
3	Station 3
4	Station 4				Matches
5	Station 5			1	Station 1
6	Station 6				Station 2
7	Station 7
8	Station 8
Sheet4

Cell Formulas
Range	Formula
D5	D5	=IF(SUMPRODUCT(--(A1:A8=E1:I1))>0,1,0)
E5:E8	E5	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$8)/ISNUMBER(MATCH($A$1:$A$8,$E$1:$I$1,0)),ROWS($E$5:$E5))),"")

 

[SleightOfHand]

Welcome to the forum!

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Station 1				Station 1	Station 2	Station A	Station B	Station C
2	Station 2
3	Station 3
4	Station 4				Matches
5	Station 5			1	Station 1
6	Station 6				Station 2
7	Station 7
8	Station 8
Sheet4

Cell Formulas
Range	Formula
D5	D5	=IF(SUMPRODUCT(--(A1:A8=E1:I1))>0,1,0)
E5:E8	E5	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$8)/ISNUMBER(MATCH($A$1:$A$8,$E$1:$I$1,0)),ROWS($E$5:$E5))),"")

Eric W, you are a gentleman and a scholar. That works!

I don't know why A:A needs to be done -- I've found it be the case when wanting to extract duplicate values with a IF/SMALL/ROW(s) too. Why does the whole thing need to be selected?

 

[Eric W]

The AGGREGATE finds the matching rows, then the A:A just says what column those rows are in.

Glad it works for you!