Evaluate Values Across Excel Worksheets Without a 3D Array Formula
Question details
Evaluate individual cells across multiple worksheets and return a result for each cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to test cells A1:A2 on several worksheets and return "t" or "f" for each value.
- Observed behavior
- The IF formula using a three-dimensional worksheet reference returns #REF! instead of evaluating each cell individually.
Identify the worksheets and cell ranges you need to evaluate. Decide whether you want individual "t" or "f" results or an aggregate count, since these require different formulas.
Evaluate each cell with a helper column on each worksheet
Use helper columns for a straightforward, maintainable result on each source worksheet.
Excel does not evaluate a three-dimensional reference cell by cell in the proposed IF formula. A helper formula evaluates each worksheet's cells separately.
On Sheet1, select B1 and enter =IF(A1=1,"t","f").
Drag the fill handle from B1 to B2 to test A2. Extend the formula down if you have more source rows.
On Sheet2 and each other source worksheet, enter the same formula in B1 and fill it down alongside the values in column A.
Check column B on each worksheet. Each cell returns "t" when the corresponding value in column A equals 1 and "f" otherwise.

Build individual worksheet references with INDIRECT
Use a sheet-name list when you want to display individual results together on a results worksheet.
Evaluate worksheet values in WPS Spreadsheet
Use helper formulas in WPS Spreadsheet to test cells on each worksheet and return an individual result for every value.
- 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose your Excel workbook.
- 2. Add a helper formula: On the first source worksheet, select B1 and enter =IF(A1=1,"t","f").
- 3. Fill the formula: Drag the fill handle from B1 down alongside the values you want to test.
- 4. Repeat and save: Repeat the formula on the other source worksheets, then select File > Save As to save the workbook.

Frequently Asked Questions
Why does my Excel 3D IF formula return #REF!?
Excel does not evaluate Sheet2:Sheet1!A1:A2 as individual cells in the proposed IF formula. Test each worksheet separately with helper formulas or construct individual references with INDIRECT.
Can I show results from several worksheets on one sheet?
Yes. List worksheet names on a results sheet and use INDIRECT to construct a reference to each source cell. Check that the listed names match the worksheet tabs.
How can I count matches instead of returning "t" or "f"?
For Sheet1 and Sheet2, use =SUMPRODUCT(--(Sheet1!A1:A2=1))+SUMPRODUCT(--(Sheet2!A1:A2=1)). This returns a combined count, not an individual result for each cell.
What if a lookup value needs to be transformed first?
Add a helper column on each source worksheet to transform the value, such as with LEFT, and then apply SUMIF or SUMIFS to that helper range.




