How to Fix Missing Comma in Excel INDEX MATCH Two-Way Lookup Formula
Question details
The user needs to correct an INDEX MATCH formula intended for a two-way lookup that is returning an error due to improper syntax between the row and column arguments.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing a two-way lookup formula using INDEX and MATCH to find a specific value at the intersection of a designated row and column.
- Observed behavior
- The formula returns a syntax error because a comma is missing between the row MATCH function and the column MATCH function. Users may also notice Excel incorrectly suggesting to multiply the MATCH expressions instead.
Verify the exact ranges of your lookup array, row criteria, and column criteria, ensuring that your lookup values exactly match the text or number formatting of the data in your arrays to prevent #N/A errors after fixing the syntax.
Insert the Missing Comma Between the Two MATCH Functions
The standard two-way lookup syntax requires the row MATCH and column MATCH to be separated by a comma, acting as the independent row_num and column_num arguments for the INDEX function.
An INDEX function is structured as INDEX(array, row_num, [column_num]). When using MATCH to dynamically find both the row and column positions, you must provide the first MATCH as the row number, add a comma, and provide the second MATCH as the column number.
Click on the cell containing your broken formula and check the formula bar. Identify the closing parenthesis where the first MATCH function ends.
Add a comma immediately after the closing parenthesis of the first MATCH function. Your formula should look like this: =INDEX(F3:M8, MATCH(A7, E3:E8, 0), MATCH(B7, F2:M2, 0)).
Ensure you are not multiplying (*) or concatenating (&) the two MATCH functions together. These operators are meant for array formulas with multiple criteria in a single dimension, not for two-way lookups.
Press Enter to execute the calculation. The correct value from the intersection of the matched row and column will now display in the cell.
Perform Advanced Lookups Easily with WPS Spreadsheet
WPS Spreadsheet provides robust support for all complex lookup functions, including INDEX, MATCH, XLOOKUP, and VLOOKUP. It helps you quickly identify syntax errors with intuitive formula prompts and argument highlighting, ensuring accurate data retrieval.
- 1. Open your spreadsheet workbook: Launch WPS Office and open your .xlsx file containing the target lookup data.
- 2. Enter the INDEX MATCH structure: Select the target cell and type =INDEX(array, MATCH(row_val, row_array, 0), MATCH(col_val, col_array, 0)).
- 3. Utilize syntax highlighting: Watch the helpful formula tooltips in WPS Spreadsheet that automatically highlight required arguments, making it easy to see exactly where a comma is needed.
- 4. Calculate the result: Press Enter to execute the two-way lookup and instantly retrieve your exact intersection data match.
Frequently Asked Questions
Why does my INDEX MATCH formula return a #REF! error after fixing the comma?
A #REF! error usually occurs if the row or column number returned by the MATCH function exceeds the boundaries of the main array defined in the INDEX function. Ensure your INDEX array (e.g., F3:M8) spans the exact same number of rows and columns as your corresponding MATCH arrays.
Can I use XLOOKUP instead of a two-way INDEX MATCH formula?
Yes, you can nest an XLOOKUP inside another XLOOKUP to perform a two-way lookup. The syntax is =XLOOKUP(row_lookup_value, row_lookup_array, XLOOKUP(column_lookup_value, column_lookup_array, return_array)). This avoids the need for strict comma separation between row and column indices.
Why is the auto-correct suggesting a multiplication sign between my MATCH functions?
The formula parser sometimes misinterprets missing arguments. Multiplying MATCH functions is a technique used for evaluating multiple criteria in a one-dimensional array formula (e.g., MATCH(1, (criteria1)*(criteria2), 0)), but it is incorrect when defining separate row and column coordinates in an INDEX function.
What does the '0' at the end of the MATCH function mean?
The '0' specifies the match type as an exact match. It forces the MATCH function to find the first value that is exactly equal to the lookup value. Without it, the function defaults to an approximate match, which requires the lookup array to be sorted alphabetically or numerically and can return incorrect results.
