Function Problems

Match Two Cells Across Excel Sheets with a Formula or VBA

Anum MushtaqAnum Mushtaq Nov 20, 2026 869 views

Question details

Find a row matching two input values across multiple worksheets and return its column C value.

Match Two Cells Across Excel Sheets with a Formula or VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to compare E1 and F1 with columns A and B on several worksheets and display the matching date or value in G1.
Observed behavior
A lookup works on one worksheet, but it needs to search multiple worksheets for the same pair of values.
Before you start

Check the worksheet names and confirm that column A contains the first lookup value, column B contains the second, and column C contains the return value. The examples assume headers are in row 1 and data begins in row 2.

Solution 1Recommended

Use a formula to search a few Excel worksheets

Use nested lookups when you have a small, known set of worksheets.

The formula searches Week1 first, then Week2, and returns the first matching value from column C. Replace the sheet names and row limits with those in your workbook.

1
Enter the lookup values

On the results sheet, enter the first value in E1 and the second value in F1.

2
Enter the lookup formula

Select G1 and enter: =IFERROR(INDEX(Week1!$C$2:$C$1000,MATCH(1,INDEX((Week1!$A$2:$A$1000=$E1)*(Week1!$B$2:$B$1000=$F1),0),0)),IFERROR(INDEX(Week2!$C$2:$C$1000,MATCH(1,INDEX((Week2!$A$2:$A$1000=$E1)*(Week2!$B$2:$B$1000=$F1),0),0)),"No Match"))

3
Format a returned date

If column C contains dates, select G1 and choose a date format from Home > Number.

Use a formula to search a few Excel worksheets
More worksheets: For each additional worksheet, add another IFERROR lookup before the final "No Match" result. The formula only searches the rows included in its ranges.
Work across spreadsheets

Match two values across sheets in WPS Spreadsheet

Use a nested INDEX and MATCH formula in WPS Spreadsheet to search a few worksheets and return the matching value or date.

  1. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check the names of the sheets you want to search.
  2. 2. Enter the lookup values: On the results sheet, enter the two values to match in E1 and F1.
  3. 3. Add the formula: Select G1 and enter: =IFERROR(INDEX(Week1!$C$2:$C$1000,MATCH(1,INDEX((Week1!$A$2:$A$1000=$E1)*(Week1!$B$2:$B$1000=$F1),0),0)),IFERROR(INDEX(Week2!$C$2:$C$1000,MATCH(1,INDEX((Week2!$A$2:$A$1000=$E1)*(Week2!$B$2:$B$1000=$F1),0),0)),"No Match"))
  4. 4. Adjust and format the result: Replace Week1 and Week2 and the row limits with your actual sheet names and ranges. If the result is a date, select G1 and apply a date number format.
Search two columns together across named worksheetsOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and worksheet tabsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

What happens if the same pair appears on more than one sheet?

The formula returns the first match in the order its sheet lookups are listed. The VBA function returns the first match in workbook worksheet order.

Why does a matching date appear as a number?

Excel stores dates as serial numbers. Select the result cell and choose a date format from Home > Number.

Why does the formula return No Match?

Check that both input values match the same row, that the sheet names are correct, and that the source row is within the formula's ranges. Also check for extra spaces or values stored as different data types.

Can the VBA function search sheets with different names?

Yes. In the VBA editor, change the condition UCase$(Left$(ws.Name, 4)) = "WEEK" to match your naming convention. The supplied function searches only sheet names that begin with Week.

Can I copy the formula down for more lookup pairs?

Yes. Enter each pair in columns E and F, then copy G1 down. The input references change by row, while the source ranges remain fixed.