Retrieve Monthly Excel Data Across Sheets with INDEX and MATCH
Question details
Retrieve monthly amounts from the Plant sheet on the MA sheet using a relationship stored on the Source sheet.

- 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.
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.
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.
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.
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))).
Select MA!C2 and drag its fill handle down for additional rows and right for additional months.

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. Open the workbook: In WPS Spreadsheet, use Open to select the workbook containing the MA, Source, and Plant sheets.
- 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. 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. Fill and save: Drag the fill handle from C2 down and across, then use Save to keep the results in the workbook.

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.




