This post will guide you how to compare two lists in different worksheets in Excel. How do I compare two lists in different worksheets with VLOOKUP function in Excel 2013/2016.

1. Comparing Two Lists in Different Worksheets

Assuming that you have two lists of data in the different worksheets, and you want to compare those two lists and try to check the values in the first list(Sheet2 – A1:A5) if it is in another list (Sheet6 – A1:A5). How to do it. You can use an Excel formula based on the VLOOKUP function, the IF function and the ISERROR function.

Here is the formula that comparing two lists in different worksheets:

= IF(ISERROR(VLOOKUP(A1,'Sheet6'!$A$1:$A$5,1,0)),"No","Yes")

You need to type this formula into cell B1 in the first worksheet that containing the first list data. And then drag the Fill Handle down to the cells to comparing other values with the second list in another worksheet.

You would notice that the checking results have been returned in column B. if it returns Yes, it indicates that this cell value is also in the second list in another worksheet. If it return No, it indicates that this cell value is not in the second list.

2: Compare Two Lists in Different Worksheets Using VBA Code

For our second method, we’ll write a VBA macro to compare the lists. This method offers more flexibility and can handle larger datasets or more complex comparisons.

press ALT + F11 to open the Visual Basic for Applications editor.

In the VBA editor, go to Insert tab and select Module to create a new module.

Copy and paste the provided VBA code into the new module.

Sub CompareLists()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long
    Dim found As Range

    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet2")
    Set ws2 = ThisWorkbook.Sheets("Sheet6")

    ' Find the last row in each list
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).row

    ' Loop through each item in the first list
    For i = 1 To lastRow1
        ' Search for the item in the second list
        Set found = ws2.Range("A1:A" & lastRow2).Find(What:=ws1.Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Output the result
        If Not found Is Nothing Then
            ws1.Cells(i, 2).Value = "Yes"
        Else
            ws1.Cells(i, 2).Value = "No"
        End If
    Next i
End Sub

In the Developer tab, click on Macros, select your newly created macro, and click ‘Run‘. Ensure your lists are in the correct worksheets and that the ranges are properly defined.

After running the macro, you’ll see the comparison results in the second column of your first list. A “Yes” indicates the item is present in the second list, while a “No” indicates it is not.

This VBA macro compares the items in the first column of “Sheet2” with those in “Sheet6” and outputs the results in the second column of “Sheet2“. Make sure to adjust the sheet names and ranges if necessary to match your specific workbook setup.

3. Video: Compare Two Lists in Different Worksheets

This Excel video tutorial, we’re going to learn how to compare two lists located in different worksheets. We’ll explore a method using a combination of Excel functions and another method using VBA code for more advanced control.

4. Related Functions

• Excel IF function
  The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel VLOOKUP function
  The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
• Excel IFERROR function
  The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….