Index Match 2 separate fields in 2 sheets

[jcneedshelp]

Hi,

Excel/Gsheet amateur here
I need to index match 2 columns from 2 separate sheets. Please Help
Example attached
I need to match Columns A and B from Sheet 1 to find out if they appear in A and B in sheet 2

Need Help Index Match

Sheet1 MJ,MN 73,2661 73,2687 73,2727 73,2729 73,2761 73,2785 73,2795 73,2849 73,2861 73,2863 73,3023 73,3031 73,3267 73,3421 73,3579 73,3937 73,5021 73,5395 73,5405 73,5415 73,5427 73,5431 73,5433 73,5439 73,5457 73,5463 73,5469 73,5587 73,5607 73,5609 73,5611 73,5627 73,5629 73,5631 73,5653 73,...

docs.google.com

Thank you in advance

 

[etaf]

countifs()
=COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,Sheet1!B2)

then you can use an IF()

=IF( COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,Sheet1!B2)>0, "match", "")

Need Help Index Match.xlsx
	A	B	C
1	MJ	MN
2	73	2661
3	73	2687
4	73	2727
5	73	2729
6	73	2761
7	73	2785
8	73	2795
9	73	2849
10	73	2861
11	73	2863
12	73	3023
13	73	3031
14	73	3267
15	73	3421
16	73	3579
17	73	3937
18	73	5021
19	73	5395
20	73	5405
21	73	5415
22	73	5427
23	73	5431
24	73	5433
25	73	5439
26	73	5457
27	73	5463
28	73	5469
29	73	5587
30	73	5607
31	73	5609
32	73	5611
33	73	5627
34	73	5629
35	73	5631
36	73	5653
37	73	5655
38	73	5661
39	73	5665
40	73	5667
41	73	5677
42	73	5681
43	73	5687
44	73	5689
45	73	5701
46	73	5703
47	73	5705
48	73	5707
49	73	5733
50	73	5735
51	73	5757
52	73	5761
53	73	5771
54	73	5773
55	73	5797
56	73	5799
57	73	5801
58	73	5809
59	73	5825
60	73	5845
61	73	5859
62	73	5863
63	76	36939	match
64	76	37011
65	76	37021
66	76	37067
67	76	37073	match
68	76	37145
69	76	37693
70	76	38217
71	76	38235
72	76	38243
73	76	38319
74	76	38333
75	76	39193	match
76	76	39955
77	79	40067
78	76	40077
79	79	40089
80	76	40105
81	79	40105
82	76	40147
83	79	40147
84	79	40151
85	79	40153
86	79	40271
87	79	40299
88	79	40303
89	79	40305
90	79	40309
91	79	40319
92	79	40357
93	79	40477
94	79	40491
95	79	40505
96	79	40545
97	79	40561
98	79	40627
99	79	40629
100	79	40637
Sheet1

Cell Formulas
Range	Formula
C2:C100	C2	=IF(COUNTIFS(Sheet2!A:A,Sheet1!A2,Sheet2!B:B,Sheet1!B2)>0,"match","")

Need Help Index Match.xlsx
	A	B
1	MJ	MN
2	78	43271
3	76	54439
4	78	43261
5	84	3223
6	76	54405
7	78	42957
8	78	42475
9	78	43481
10	78	43009
11	78	43267
12	78	43253
13	78	43367
14	78	43117
15	78	43297
16	78	42257
17	78	46945
18	78	42239
19	84	6887
20	84	7535
21	84	7571
22	84	6923
23	84	6927
24	84	5617
25	84	7185
26	84	6617
27	84	7325
28	84	6953
29	83	27507
30	78	15049
31	78	47665
32	81	35855
33	78	46417
34	76	55561
35	78	46973
36	78	42391
37	78	43163
38	78	46833
39	78	42977
40	78	43111
41	78	47609
42	75	19151
43	79	40949
44	78	43001
45	75	19205
46	73	5843
47	76	36939
48	78	42959
49	78	43363
50	79	41617
51	78	46899
52	76	48417
53	78	42479
54	78	43263
55	78	42825
56	77	24623
57	78	42401
58	78	46495
59	78	42473
60	76	37073
61	76	39193
62	78	42695
63	78	43057
64	78	59451
65	79	43507
66	78	42235
67	77	24953
68	78	43255
69	78	47027
70	78	42969
71	82	61641
72	78	43359
73	78	47013
74	76	54425
75	78	42243
76	78	42289
77	78	43307
78	78	43123
79	78	46413
80	79	40905
81	76	48157
82	76	55545
83	77	41065
84	77	42801
85	73	5709
86	79	40277
87	76	56175
88	75	19045
89	77	19599
90	77	41177
91	79	43349
92	79	41619
93	83	5315
94	79	41843
95	79	40749
96	77	41165
97	78	43231
98	76	56091
99	73	5811
Sheet2

i only keep on dropbox for a

Need Help Index Match-etaf.xlsx

Shared with Dropbox

www.dropbox.com

 

[jcneedshelp]

Thanks, so I would just use the second formula in column C on sheet 2?

 

[etaf]

sorry dont understand

where do you want the match to show up

I need to match Columns A and B from Sheet 1 to find out if they appear in A and B in sheet 2

I assumed from above to show in sheet1 if they match in sheet2

2nd formula?

 

[jcneedshelp]

It works, in sheet 1 column 2. thanks so much for your help.

 

[etaf]

sorry confused , the formula is in column C not column 2 ?????
anyway
seems its working now as needed

so ,
you are welcome

 

[jcneedshelp]

Yes, sorry typo, Column C

Much appreciated.