Help On: Comparing lists by rows with multiple columns and cells with different data types

[randomhause]

Hello kind strangers ~ I need some help with the following scenario in Excel...

There are two lists in separate workbooks that need to be compared, Master List and Sample List.

The rows in the Master List need to be matched exactly to the Sample List, which has more rows but same number of columns.

So row A2 in Master List needs to be searched for in the Sample List workbook. Ideally would like to have a function that will return a "N/a" if a Master List row doesn't appear in the Sample List and a "Matched" if it does.

Details about both lists:

Master List - A2:G2, 1240 Rows
Sample List - A2:G2, 1353 Rows

Year	Term	Session	Event	Section	Name	Credits
####	AAA	BBB	CC####	D##	EEEE	#.##

<tbody>
</tbody>

Limitations:
I cannot go row by row because the rows in the Sample list are not in order (but the columns are). For example:

	Master List
A1	Year	Term	Session	Event	Section	Name	Credits
A2	2015	Fall	First	ACC8500	A01	Generic Name	0.75
A3	2016	Spring	Full	MKT7500	01	Generic Name Two	1

<tbody>
</tbody>

	Sample List
A1	Year	Term	Session	Event	Section	Name	Credits
A2	2016	Spring	Full	MKT7500	01	Generic NameTwo	1
A3	2015	Fall	First	ACC8500	A01	Generic Name	0.75

<tbody>
</tbody>

Also, I don't need to join the data as Sample List includes additional rows. I just need to verify that the information in Master List does appear in the Sample List.

What I have tried:

1. On Simple List H2=MATCH(A2:G2,'[MasterList.xlsx]Sheet1'!$A$2:$G$1243,0)
   1. This returns "#VALUE" because of the cells that have different data types (text and numeric values).
2. A variation of =VLOOKUP(A2,[MasterList.xlsx]Sheet1!$A$2:$G$1353,1,0)
   1. ​however doesn't work as in the Sample list the information is not organized the same.

I Appreciate Your Time In Helping Me

 

[Jerry Sullivan]

Hi randomhause,

Can the Event field value be used as a unique match, or do all 8 fields have to be compared? I understand that you are wanting to compare lists to find rows with all 8 fields matching. If there will never be a case where the Event value matches and the other fields are different, then it would be simplest just to test for a matching event.

In Cell H2 of Workbook Sample List:
=IF(ISNUMBER(MATCH(D2,[MasterList.xlsx]Sheet1!$D:$D,0)),"Matched","N/a")

If you need to test all 8 fields, that could be done with a more complex formula or through the use of a helper column that concatenates the field values together into a match key phrase.

 

[Chrisdontm]

What I would do is Concatenate the row into Column H on both sheets; If you are using Excel 2010 do this; =CONCATENATE(A2,B2,C2,D2,E2,F2,G2) If you are using 2016 do this; =CONCAT($A2:$G2) Then in cell I2 do this; =IF(ISNUMBER(MATCH($H2,'[MasterList.xlsx]Sheet1'!$H:$H,0)),"Matched","N/A") Then copy all the formulas down... If you want to hide column "H", it will still work...

 

[randomhause]

Hello!

Thank you I was able to use @Chrisdontm way to solve this.