Function Problems

Retrieve Monthly Excel Data Across Sheets with INDEX and MATCH

Adam DavisAdam Davis Nov 18, 2026 868 views

Question details

Retrieve monthly amounts from the Plant sheet on the MA sheet using a relationship stored on the Source sheet.

Retrieve Monthly Excel Data Across Sheets with INDEX and MATCH
Product
Microsoft Excel
Device & OS
not provided
Scenario
The MA and Plant sheets have no direct common field, but the Source sheet relates an account or name to a Plant type.
Observed behavior
The desired result is for each MA row and month column to show the corresponding amount from the Plant sheet.
Before you start

Check that MA column B contains the values to look up in Source column C, and that Source column A contains the corresponding Plant types. Confirm that Plant column A contains those types and that the month headers on MA and Plant match.

Solution 1Recommended

Use INDEX and MATCH to look up the type and month

Use the Source sheet as a bridge when MA and Plant do not share a direct lookup field.

The first lookup finds the type associated with the value in MA column B. The formula then matches that type and the month header to return the amount from Plant.

1
Check the source ranges

On Source, confirm that A2:A23 contains types and C2:C23 contains the account or name values used in MA column B. On Plant, confirm that A2:A14 contains types, B1:M1 contains months, and B2:M14 contains amounts.

2
Enter the formula

On the MA sheet, select C2 and enter =IF($B2="","",INDEX(Plant!$B$2:$M$14,MATCH(INDEX(Source!$A$2:$A$23,MATCH($B2,Source!$C$2:$C$23,0)),Plant!$A$2:$A$14,0),MATCH(C$1,Plant!$B$1:$M$1,0))).

3
Fill the results

Select MA!C2 and drag its fill handle down for additional rows and right for additional months.

Use INDEX and MATCH to look up the type and month
Adapt the ranges: Adjust the sheet names and ranges if your layout differs. MATCH returns the first exact match, so check for duplicate lookup values or types if the result is unexpected.
Work with spreadsheets in WPS Office

Retrieve Monthly Data Across Sheets in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use the same INDEX and MATCH formula to connect MA values to Plant amounts through the Source sheet.

  1. 1. Open the workbook: In WPS Spreadsheet, use Open to select the workbook containing the MA, Source, and Plant sheets.
  2. 2. Verify the layout: Check Source columns A and C for types and lookup values, Plant column A for types, and Plant B1:M1 for month headers.
  3. 3. Enter the lookup formula: On MA, select C2 and enter =IF($B2="","",INDEX(Plant!$B$2:$M$14,MATCH(INDEX(Source!$A$2:$A$23,MATCH($B2,Source!$C$2:$C$23,0)),Plant!$A$2:$A$14,0),MATCH(C$1,Plant!$B$1:$M$1,0))).
  4. 4. Fill and save: Drag the fill handle from C2 down and across, then use Save to keep the results in the workbook.
Use INDEX and MATCH to retrieve monthly amounts across worksheets.Open and edit Microsoft Excel XLSX workbooks.Fill a formula across months and down rows.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #N/A?

An exact match may be missing in Source!C2:C23, Plant!A2:A14, or Plant!B1:M1. Check the value in MA column B, the corresponding type, and the month header for differences such as extra spaces or mismatched dates.

What happens if an account appears more than once on the Source sheet?

MATCH returns the first exact match in Source!C2:C23. Check for duplicates if multiple rows could map the same account or name to different types.

Why is the result blank when MA column B is empty?

The IF($B2="","",...) part of the formula intentionally returns a blank when B2 is empty, so no lookup is attempted.

How do I include more rows or months?

Edit the formula in MA!C2 to expand the relevant Source and Plant ranges. Keep the Source type and lookup ranges aligned, and make sure the Plant type range, month headers, and amount range cover the corresponding rows and columns.