Use VBA to Match Two Excel Columns and Return a Lookup Value
Question details
Match pairs of values across two Excel worksheets and return the related value from a third column using VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Sheet1 and Sheet2 contain values in columns A and B, and the user wants to return the corresponding value from Sheet2 column C to Sheet1 column C.
- Observed behavior
- The user also encounters a Sub or Function not defined error when trying a VBA solution.
Confirm that the worksheets are named Sheet1 and Sheet2, row 1 contains headers, and columns A and B contain lookup values without Excel error values. Sheet1 column C must be available for results because the macro clears its existing values for the rows it processes.
Run a VBA macro to match both columns and return the value
Use this macro to compare each A-and-B pair on Sheet1 with Sheet2 and return the first matching value from Sheet2 column C.
The macro skips Sheet1 rows with a blank value in either lookup column. It compares values as text without regard to letter case and leaves column C blank when no match is found.
In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select the workbook and choose Insert > Module.
Paste this code into the standard module: Sub MatchTwoColumns() Dim ws1 As Worksheet, ws2 As Worksheet Dim last1 As Long, last2 As Long Dim i As Long, j As Long Set ws1 = ThisWorkbook.Worksheets("Sheet1") Set ws2 = ThisWorkbook.Worksheets("Sheet2") last1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row last2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row For i = 2 To last1 ws1.Cells(i, "C").ClearContents If Len(CStr(ws1.Cells(i, "A").Value2)) > 0 And Len(CStr(ws1.Cells(i, "B").Value2)) > 0 Then For j = 2 To last2 If StrComp(CStr(ws1.Cells(i, "A").Value2), CStr(ws2.Cells(j, "A").Value2), vbTextCompare) = 0 And _ StrComp(CStr(ws1.Cells(i, "B").Value2), CStr(ws2.Cells(j, "B").Value2), vbTextCompare) = 0 Then ws1.Cells(i, "C").Value = ws2.Cells(j, "C").Value Exit For End If Next j End If Next i End Sub
Use File > Save As to save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Return to Excel, select Developer > Macros, choose MatchTwoColumns, and click Run.
Open Sheet1 and review column C. If the worksheet names or data columns differ, update the worksheet names and column letters in the module before running the macro again.

Troubleshoot the Sub or Function not defined error
Use this when Excel highlights an unrecognized procedure or function while compiling or running VBA code.
Match Two Columns Without VBA in WPS Spreadsheets
Use a helper column and an INDEX/MATCH formula to find the first matching pair and return its related value in WPS Spreadsheets.
- 1. Open the workbook: Open the workbook in WPS Spreadsheets. Confirm that the sheets are named Sheet1 and Sheet2, and that Sheet2 column D and Sheet1 column C are available for the helper keys and results.
- 2. Create lookup keys: On Sheet2, enter =IF(OR(A2="",B2=""),"",A2&CHAR(1)&B2) in D2, then drag the fill handle down through the lookup rows.
- 3. Enter the lookup formula: On Sheet1, enter =IF(OR(A2="",B2=""),"",IFERROR(INDEX(Sheet2!$C$2:$C$1000,MATCH(A2&CHAR(1)&B2,Sheet2!$D$2:$D$1000,0)),"")) in C2. Adjust the range endpoints if Sheet2 has data beyond row 1000.
- 4. Fill and review: Drag the fill handle from Sheet1 cell C2 down through the rows to match. Check column C for returned values; unmatched pairs remain blank.

Frequently Asked Questions
What happens if the same pair appears twice on Sheet2?
The VBA macro stops at the first matching row and returns that row's column C value. The WPS INDEX/MATCH formula also returns the first match.
Why does the macro leave a result blank?
Check that both Sheet1 lookup cells contain values and that the pair exists in Sheet2 columns A and B. The macro also returns a blank if the matching Sheet2 column C cell is blank.
How do I find the cause of a Sub or Function not defined error?
Click Debug in the error dialog and inspect the highlighted name. Use Edit > Find to check its spelling and definition, then check Tools > References for missing libraries.
Can I match the columns without using VBA?
Yes. In WPS Spreadsheets, create a combined key in Sheet2 column D and use INDEX and MATCH on Sheet1 to return the related value from Sheet2 column C.




