'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use IF and MATCH Array Formulas with Ctrl+Shift+Enter in Excel
Question details
The user needs to understand how Ctrl+Shift+Enter forces array evaluation in legacy Excel when using MATCH, and whether wrapping functions like IF and N() are strictly required.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using MATCH with multiple lookup values in older Excel versions where dynamic arrays are not supported natively.
- Observed behavior
- Without IF wrappers and Ctrl+Shift+Enter, MATCH applied to multiple lookup ranges returns only the first value or an error instead of an array of positions.
Verify your Excel version, as Microsoft 365 natively supports dynamic arrays without needing Ctrl+Shift+Enter or complex IF wrappers to force array evaluation.
Using IF and N() to Force Array Evaluation in MATCH
Wrap your MATCH function with IF(1,...) and optionally N() to force legacy Excel versions to return an array of values instead of a single result.
In legacy Excel (pre-Microsoft 365), functions like MATCH were not originally designed to return arrays for multiple lookup values. Wrapping MATCH inside an IF statement with a TRUE condition (like IF(1,...)) tricks Excel into processing the array.
The N() function is sometimes added to coerce these results explicitly into numeric values, ensuring outer functions like INDEX process the array properly.
Click on the cell where you want to output your calculation, such as a SUM or INDEX formula.
Type your MATCH function designed to find multiple values, for example: MATCH(E5:G5, B5:B8, 0).
Wrap the MATCH formula inside IF and N to force array behavior. The formula should look like: N(IF(1, MATCH(E5:G5, B5:B8, 0))).
Complete your outer formula (e.g., =SUM(INDEX(C5:C8, N(IF(1, MATCH(E5:G5, B5:B8, 0)))))) and press Ctrl+Shift+Enter instead of just Enter. Excel will add curly braces {} around the formula, confirming it is evaluated as an array.
Using MATCH Array Formulas in Modern Excel (Microsoft 365)
For modern Excel versions, evaluate array formulas natively without the need for Ctrl+Shift+Enter or complex IF wrappers.
Handle Array Formulas Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas, including Ctrl+Shift+Enter legacy evaluations and modern array processing. You can effortlessly use MATCH, IF, and INDEX arrays to calculate complex data structures.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the formula: Select the target cell and type your array formula, including MATCH and any required IF wrappers.
- 3. Evaluate the array: Press Ctrl+Shift+Enter. WPS Spreadsheet will automatically enclose the formula in curly braces {}, evaluating it correctly.
Frequently Asked Questions
Why does my MATCH array formula return an error in older Excel versions?
In older Excel versions, MATCH cannot natively return an array of multiple lookup values unless it is forced into an array context. You must wrap it in functions like IF and evaluate it using Ctrl+Shift+Enter.
What does IF(1,...) do in an array formula?
The IF(1,...) syntax uses '1' as a TRUE condition to act as a wrapper. It forces Excel's calculation engine to process the nested MATCH function as an array rather than returning just the first matched value.
Is the N() function strictly required for MATCH arrays?
No, the N() function is optional. It is primarily used to coerce values (such as booleans or text numbers) into proper numeric values, ensuring compatibility with outer functions like INDEX.
How do I know if an array formula is working correctly?
When you successfully enter an array formula using Ctrl+Shift+Enter, Excel automatically surrounds the entire formula in the formula bar with curly braces { }. If you do not see these braces, the formula was entered as a standard formula and may return an error.
