INDEX/MATCH Function very slow!! Help!!

[Verysheyn]

Hello Everyone,

So I'm trying to compare 2 tables (an Old one and a New one) and I want to validate that everything on the Old one is contained in the New one. Both tables are just 1 column and about 17500 rows.

Each value on each cell of both tables is about 400 characters.

The formula below is the one I'm using to match them up since VLOOKUP Formula won't do the trick in this case since every value is more than 255 characters long.

In Cell B2
=IFERROR(IF(INDEX('New Table'!$A$2:$A$17436,MATCH(TRUE,INDEX('New Table'!$A$2:$A$17436=A2,0),0))<>"NA","Correct","Missing"),"Missing")

In Cell B3
=IFERROR(IF(INDEX('New Table'!$A$2:$A$17436,MATCH(TRUE,INDEX('New Table'!$A$2:$A$17436=A3,0),0))<>"NA","Correct","Missing"),"Missing")

and so on...

When excel is calculating everything is just so slow!! Can anyone help making it faster?? maybe another formula or another approach?



Thanks!!

Verysheyn

 

[AhoyNC]

Maybe you could shorten your formula to something like below.

Excel Workbook
	A	B	C
1	Old	Not in New	New
2	Name1	Correct	Name1
3	Name2	Correct	Name2
4	Name3	Correct	Name3
5	Name4	Correct	Name4
6	Name5	Missing	Name7
7	Name6	Missing	Name8
8	Name7	Correct	Name9
9	Name8	Correct	Name11
10	Name9	Correct	Name12
11	Name10	Missing	Name13
12	Name11	Correct	Name14
13	Name12	Correct	Name15
14	Name13	Correct	Name16
15	Name14	Correct	Name17
16	Name15	Correct	Name18
17	Name16	Correct	Name19
Sheet

 

[Verysheyn]

Hello AhoyNC,

That doesn't seem to work. Somehow the Match function always throws up a #VALUE! error


Verysheyn

 

[AhoyNC]

It would probably help if you could post a small sample of your data (is it text, numeric, etc.), then someone may be able to help you shorten the formula you have. The IFERROR function can slow things down if you have a large data set.

Also, take a look at the following link for Excel is Fun that shows different options to compare lists.

https://www.youtube.com/user/ExcelIsFun/search?query=Compare+lists