How to Transfer Data Between Excel Sheets by Location and Week
Question details
The user needs a formula to automatically transfer and populate data from a source worksheet to a destination worksheet based on matching location names (rows) and week headings (columns).

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Populating a destination sheet with weekly data by looking up intersecting location and week headers from a source sheet, and ensuring the formula can be dragged across and down.
- Observed behavior
- Data needs to dynamically populate the correct row and column intersections and allow copying the formula across multiple cells without reference errors.
Ensure both your source and destination sheets have matching location names in the rows and identical week headings in the columns to allow the lookup formulas to work correctly.
Use INDEX and MATCH for Two-Dimensional Lookup
This is the most robust method for transferring data across rows and columns based on two criteria.
The INDEX and MATCH combination allows you to perform a two-way lookup. It dynamically matches the location in the rows and the week in the columns to fetch the exact intersecting value from your source data.
Open your destination sheet (e.g., Sheet2) and click on the first empty cell where you want the data to appear (e.g., cell B2).
Type the formula: =IFERROR(INDEX(Sheet1!$B$2:$ZZ$100,MATCH($A2,Sheet1!$A$2:$A$100,0),MATCH(B$1,Sheet1!$B$1:$ZZ$1,0)),"")
Press Enter. Then, click the fill handle in the bottom-right corner of the cell and drag it across the columns and down the rows to apply the formula to the rest of your table.

Use VLOOKUP Combined with MATCH
An alternative approach using VLOOKUP to find the row and MATCH to determine the correct column index.
Use WPS Spreadsheet to Transfer and Manage Complex Data Easily
WPS Office provides a highly compatible and lightweight Spreadsheet application that fully supports advanced formulas like INDEX, MATCH, and VLOOKUP. You can easily manage weekly tracking and automate data transfers across sheets.
- 1. Install WPS Office: Download and install WPS Office on your device.
- 2. Open your workbook: Launch WPS Spreadsheet and open your data tracking workbook.
- 3. Enter the lookup formula: Enter your INDEX and MATCH formula in the destination cell to link your data.
- 4. Drag to populate: Use the intuitive fill handle to drag and populate your entire data grid instantly.

Frequently Asked Questions
Why is my INDEX MATCH formula returning blank cells?
This usually happens if the location names or week headings do not exactly match between the two sheets. Check for trailing spaces or spelling differences. The IFERROR wrapper in the formula forces it to return a blank instead of an #N/A error when a match isn't found.
What do the dollar signs ($) in the Excel formula mean?
The dollar signs create absolute references. For example, $A2 locks the column so it always looks at column A for the location, while B$1 locks the row so it always looks at row 1 for the week heading when you drag and copy the formula.
How do I adjust the formula if my data goes beyond row 100?
Simply change the row number in the formula's ranges. For example, change Sheet1!$B$2:$ZZ$100 to Sheet1!$B$2:$ZZ$500 in both the INDEX array and the MATCH lookup array to accommodate more data.
Can I use XLOOKUP for transferring data by row and column?
Yes, if your version of your spreadsheet software supports it, you can nest two XLOOKUP functions to perform a two-way lookup. However, INDEX and MATCH remains the most universally compatible method across all older and newer spreadsheet versions.




