Match Two Cells Across Excel Sheets with a Formula or VBA
Question details
Find a row matching two input values across multiple worksheets and return its column C value.

- 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.
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.
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.
On the results sheet, enter the first value in E1 and the second value in F1.
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"))
If column C contains dates, select G1 and choose a date format from Home > Number.

Use a VBA function to search Week worksheets
Use a VBA function when many worksheets have names beginning with Week.
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. Open the workbook: Open your Excel workbook in WPS Spreadsheet and check the names of the sheets you want to search.
- 2. Enter the lookup values: On the results sheet, enter the two values to match in E1 and F1.
- 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. 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.

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.




