Look-up combination of INDEX(array, (MATCH()) in a table returns #VALUE error

[astrbac]

Hi all,

I have two data sets (two sheets in the same workbook) where I need to locate entries from one in the other one. Both sets are formatted as a Table and I am using an INDEX(MATCH()) combination to match entries. If match is found, values from one of the fields in Dataset1 should appear in an added column of the Dataset2. That way I know there is a match, I don't really care about the value returned.

Unfortunately, I do not have any uniqueID to match on so I have to use a combination of two fields in each of the tables to try and produce matches. Fields are [@Name] and [@Surname] which is less then ideal in Croatian language, since the name Adis Terzic will obviously not match Adis Terzić.

The problem is:
added column (field) in Dataset2 does not show any values returned from the Dataset1 but rather all it shows is a #VALUE error. Any ideas what I am doing wrong?

My syntax is along the following lines:

=INDEX(Table1[@Field4],MATCH(Table1[@Name trim]&Table1[Surname trim], Table2[Name]&Table2[Last name], 0))

Here is an example workbook (Google Drive).

Dataset1 (Table1) has about 28000 rows and 16 columns;
Dataset2 (Table2) has about 24000 rows and 7 columns;

Many thanks in advance!
Alex

 

[njimack]

Did you confirm with ctrl+shift+enter:

If you're not concerned by the returned value and simply want to confirm that the data matches, consider this:

=SUMPRODUCT(--(Table1[@Name trim]=Table2[Name]),--(Table1[@Surname trim]=Table2[Last name]))>0

 

[astrbac]

Hey Neil,

thanks for the quick reply! Ctrl+Shift+Enter also does the same thing (returns #VALUE errors). I believed that I didn't need to do the "array formula" thing when entering the formula in the first row of a Table, It normally gets entered as an array formula all by itself (and extends down all the rows).

SUMPRODUCT() works much better as it returns TRUE/FALSE.

The problem with this is that there are more entries (rows) in one of the Datasets so I am again getting #VALUE errors in the last couple thousand rows, which do not exist in the other dataset. First table is about 28.000 rows and the other is 24000.

Also, if I'm not mistaken, SUMPRODUCT() approach would work if the fields in both tables were arranged aslpabetically and not random?

Table 1
Marc
Anthony
Steve

Table 2
John
Steve
Marc

will not work (all three rows would be false). Or?

 

[njimack]

SUMPRODUCT doesn't require the data to be sorted so in your example, it would return 1 for Marc and 0 for everyone else.

 

[astrbac]

But it should match Steve also, no? :/