Find Dates on Another Sheet by Status with INDIRECT and INDEX MATCH
Question details
The user wants to retrieve specific dates from separate worksheets by searching for a status term (like 'Approved') in the comments column, dynamically identifying the target sheet using a cell reference.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user manages multiple documents with individual review worksheets and needs to pull the exact date a document was approved into a central master sheet.
- Observed behavior
- Using a combined formula of INDIRECT, INDEX, and MATCH successfully creates a dynamic reference to the target sheet, searches the comments column for the specified status, and returns the date from the same row.
Verify that the worksheet names typed in your master summary sheet match the actual tab names exactly, including any spaces, to avoid #REF! errors when the INDIRECT function tries to locate them.
Combine INDIRECT with INDEX and MATCH
Use the INDIRECT function to dynamically build the sheet reference based on a cell value, and use INDEX and MATCH to locate the target text and extract the corresponding date.
This formula combination is especially useful when every document has its own dedicated review worksheet. It allows you to search for a specific status, such as 'Approved', within a comments column and output the exact date that corresponds to it on your main summary sheet.
List the exact names of your target worksheets in column A (for example, A2 contains the text 'Sheet2') and the search terms in column C (for example, C2 contains 'Approved').
Select the cell where you want to display the retrieved date (e.g., D2) and enter the following formula: =INDEX(INDIRECT("'"&A2&"'!$A$5:$A$7"),MATCH(TRUE,ISNUMBER(SEARCH(C2,INDIRECT("'"&A2&"'!$B$5:$B$7"))),0),1)
Depending on your spreadsheet version, you may need to press Ctrl+Shift+Enter to evaluate this as an array formula. Make sure to adjust the ranges ($A$5:$A$7 for dates, $B$5:$B$7 for comments) to match the actual layout of your referenced sheets.
Calculate Dynamic Cross-Sheet References in WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced array formulas including INDIRECT, INDEX, and MATCH. Seamlessly look up data across multiple worksheets with high performance and complete Excel compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your master sheet and multiple review worksheets.
- 2. Set up your reference cells: Ensure you have the exact worksheet names typed into your master sheet (e.g., in column A) and your search keywords (e.g., 'Approved') in another column.
- 3. Enter the formula: Select the target cell and enter the combined =INDEX(INDIRECT(...)) formula into the formula bar to pull data from the designated sheets.
- 4. Apply array formula if needed: Press Enter to execute the function, or use Ctrl+Shift+Enter if you are working with legacy spreadsheet formats requiring array entry.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error typically indicates that the sheet name referenced by the INDIRECT function does not exist, contains a typo, or includes extra spaces. It also occurs if the referenced external workbook is currently closed.
Do I need single quotes around the sheet name in the INDIRECT formula?
Yes, wrapping the sheet name in single quotes (e.g., "'"&A2&"'!") is crucial if your worksheet names contain spaces or special characters. It is highly recommended to always include them to prevent reference errors.
Can I use XLOOKUP instead of INDEX and MATCH?
Yes, if you are using modern spreadsheet software like WPS Spreadsheet or newer versions of Excel, you can combine INDIRECT with XLOOKUP to create a simpler, more intuitive lookup formula without the need to handle array outputs manually.
