Function Problems

How to Transfer Data Between Excel Sheets by Location and Week

Olivia MillerOlivia Miller Oct 8, 2026 870 views

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).

How to Transfer Data Between Excel Sheets by Location and Week
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

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).

2
Enter the INDEX and MATCH formula

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)),"")

3
Apply the formula to the table

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 INDEX and MATCH for Two-Dimensional Lookup
Absolute References: Make sure to adjust the ranges (like Sheet1!$B$2:$ZZ$100) to match your actual source data. The $ signs lock the ranges so they don't shift improperly when copying the formula.
Efficient Spreadsheet Management

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. 1. Install WPS Office: Download and install WPS Office on your device.
  2. 2. Open your workbook: Launch WPS Spreadsheet and open your data tracking workbook.
  3. 3. Enter the lookup formula: Enter your INDEX and MATCH formula in the destination cell to link your data.
  4. 4. Drag to populate: Use the intuitive fill handle to drag and populate your entire data grid instantly.
Fully compatible with Microsoft Excel formulas and .xlsx files.Seamlessly handle two-dimensional lookups to transfer data by location and week.Lightweight installation with a clean, user-friendly interface.Free to use for everyday data management and reporting.
microsoft office alternative - wps office

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.