Help, Index Match Lookup between multiple sheets

[MarcBK]

Hello all,

I am trying to lookup product names in a sheet I have and return the names in another sheet.

So names in sheet 1 appear in column J rows 3 - 2000

and names in sheet 2 appear in columns CF rows 3-1000

And I want to show the names from Sheet 1 only if they appear in sheet 2 in column A on sheet 3.

any help is greatly appreaciated

 

[gelu]

Hello Mark,
formula + add a filter to filter out 0 rows

Index-Match.xlsx
	A	B	C	D	E
1
2	A
3	B
5	D
6	E
8	G
9	H
11	J
12	K
13	L
17	P
19	S
20	T
21	Q
23	Y
24	Z
25			Sheet1 J	Sheet2 CF
26			A	A
27			B	B
28			C
29			D	D
30			E	E
31			F
32			G	G
33			H	H
34			I
35			J	J
36			K	K
37			L	L
38			M
39			N
40			O
41			P	P
42			R
43			S	S
44			T	T
45			Q	Q
46			X
47			Y	Y
48			Z	Z
49
Sheet3

Cell Formulas
Range	Formula
A23:A24,A19:A21,A17,A11:A13,A8:A9,A5:A6,A2:A3	A2	=INDEX(Sheet2!$CF:$CF,MATCH(Sheet1!J3,Sheet1!$J:$J,0))

 

[Fluff]

Another option

Fluff.xlsm
	A	B								J																																																																										CF
1
2
3	HA7 2QJ									BN1 7GB																																																																										HA7 2QJ
4	N13 6AU									CB25 0DU																																																																										N13 6AU
5	BL1 4LJ									LS13 3DJ																																																																										BL1 4LJ
6	WS2 9BP									ST16 3QU																																																																										WS2 9BP
7										E5 0QG																																																																										NW9 0XN
8										SG13 7FZ																																																																										LU3 1EY
9										SN2 1BD																																																																										SK22 4PD
10										TW3 1NN																																																																										UB2 5SQ
11										SL6 4ED																																																																										TN13 2BB
12										NG13 8QA																																																																										GL15 6PE
13										WA12 9RG																																																																										RM11 1EL
14										PE30 4SF																																																																										NR17 1LN
15										SG1 3AE																																																																										BS7 0QB
16										HU5 2UR																																																																										NE2 4AG
17										SL6 3NY																																																																										B37 6NJ
18										SP10 4BT																																																																										HP2 5LB
19										CO7 9RR
20										RG31 7ZU
21										NE4 6XD
22										HA7 2QJ
23										N13 6AU
24										BL1 4LJ
25										WS2 9BP
Main

Cell Formulas
Range	Formula
A3:A11	A3	=IFERROR(INDEX($J$3:$J$2000,AGGREGATE(15,6,(ROW($J$3:$J$2000)-ROW($J$3)+1)/(ISNUMBER(MATCH($J$3:$J$2000,$CF$3:$CF$1000,0))),ROWS(A$3:A3))),"")

 

[MarcBK]

Another option

Fluff.xlsm
	A	B								J																																																																										CF
1
2
3	HA7 2QJ									BN1 7GB																																																																										HA7 2QJ
4	N13 6AU									CB25 0DU																																																																										N13 6AU
5	BL1 4LJ									LS13 3DJ																																																																										BL1 4LJ
6	WS2 9BP									ST16 3QU																																																																										WS2 9BP
7										E5 0QG																																																																										NW9 0XN
8										SG13 7FZ																																																																										LU3 1EY
9										SN2 1BD																																																																										SK22 4PD
10										TW3 1NN																																																																										UB2 5SQ
11										SL6 4ED																																																																										TN13 2BB
12										NG13 8QA																																																																										GL15 6PE
13										WA12 9RG																																																																										RM11 1EL
14										PE30 4SF																																																																										NR17 1LN
15										SG1 3AE																																																																										BS7 0QB
16										HU5 2UR																																																																										NE2 4AG
17										SL6 3NY																																																																										B37 6NJ
18										SP10 4BT																																																																										HP2 5LB
19										CO7 9RR
20										RG31 7ZU
21										NE4 6XD
22										HA7 2QJ
23										N13 6AU
24										BL1 4LJ
25										WS2 9BP
Main

Cell Formulas
Range	Formula
A3:A11	A3	=IFERROR(INDEX($J$3:$J$2000,AGGREGATE(15,6,(ROW($J$3:$J$2000)-ROW($J$3)+1)/(ISNUMBER(MATCH($J$3:$J$2000,$CF$3:$CF$1000,0))),ROWS(A$3:A3))),"")

Thank you for the help,

I ended up working it out.

=FILTER(Sheet 1 Name,COUNTIF(sheet 2 Name,sheet 1 name))

 

[Fluff]

If you are using the Filter function then you are not using 2019, I would recommend updating your profile.