In Excel, how to compare 1 cell against a column of values in two workbooks

excel multiple-columns vlookup
user3892897 picture user3892897 · Jul 30, 2014 · Viewed 70.6k times · Source

Tried to find an answer but could not - I am an excel novice.

Our company put on a webinar, we received excel spreadsheet of attendees, names company names, etc.

I am tasked with comparing the Attendee list/spreadsheet with our company CRM database to see if they are already in the system. Comparing by company name only.

I created an export from our CRM system of all companies we have in the CRM system as a single column excel file (column A, company name, 732 lines).

I need to compare the CRM export xls column A to the Attendee Spreadsheet, Company Name (column E2-E180) and flag each one that already exists in the CRM system.

i would like to put a "NO MATCH" or "MATCH" text on the Attendee Sheet in column F on the row of the company that was not found.

Can anyone give some suggestions/formulas so I do not have to perform this manually one by one.

Answer

Steve W picture Steve W · Jul 30, 2014 
=IF(COUNTIF($D$2:$D$5,A2)>0, "Match", "No Match")

Use this where D2:D5 is the list of existing companies and A2 is the cell containing the company that you want to search for.

Related questions

Multiple input box excel VBA

Right now I've been using multiple Input boxes to get user input for a couple of strings i need: Dim U As String Dim P As String U = Application.InputBox("Enter Username", "Username", Type:=1)) P = Application.InputBox("Enter Pwd", "pwd", …

Read More
Excel - SUMIFS for multiple columns

I need to sum the values of several columns, if other cells in the same row match a predefined criteria. The working formula for only 3 columns is the following: =SUM(SUMIFS(‘Sheet1'!W:W; ‘Sheet1'!$B:$B;"Sales";‘…

Read More
How to consolidate duplicates in Excel where several columns need to remain uncosolidated

I am trying to do a stock count through product stores but have several part numbers that are listed twice (Column A). When I try to consolidate these I recieve the error 'no data consolidated' which I think is caused …

Read More