Function Problems

Combine Excel INDEX MATCH with LEFT and FIND for Text Extraction

Camila Milosovich Oct 12, 2026 872 views

Question details

The user needs to look up a value using INDEX and MATCH, and then extract only the text appearing before an opening parenthesis using the LEFT and FIND functions.

How to Combine Excel INDEX, MATCH, LEFT, and FIND Functions

Product: Excel
Device & OS: not provided
Scenario: Cleaning and extracting data from a lookup result that contains mixed text and symbols, such as isolating 'Apple' from 'Apple (500)'.
Observed behavior: Without error handling, the formula may return a #VALUE! error if a parenthesis is missing or if the sheet references contain typos (like 'worsheet1' instead of 'worksheet1').

Before you start

Verify that your lookup table and source data are correctly aligned, and ensure the target text strings consistently contain the opening parenthesis to minimize formula errors.

Solution 1Recommended

Use a Combined Formula with IFERROR

Combining IFERROR, LEFT, INDEX, MATCH, and FIND creates a robust formula that extracts text while preventing #VALUE! or #N/A errors when data is missing.

The INDEX and MATCH functions work together to look up the correct cell. Once the value is retrieved, FIND locates the position of the opening parenthesis, and LEFT extracts all text before that position.

Using IFERROR is crucial because it ensures that if no match is found, or if the parenthesis is missing from the result string, the cell displays a clean message instead of a generic error code.

Select the destination cell

Click on the cell in your worksheet where you want the extracted text to be displayed.

Enter the formula

Type the following formula: =IFERROR(LEFT(INDEX('reference'!B:B,MATCH(worksheet1!$G24,'reference'!C:C,0)),FIND("(",INDEX('reference'!B:B,MATCH(worksheet1!$G24,'reference'!C:C,0)))-1),"No match found").

Adjust the references

Replace 'reference'!B:B, worksheet1!$G24, and 'reference'!C:C with the actual sheet names and data ranges relevant to your workbook.

Apply to multiple cells

Press Enter to execute the formula, then click and drag the fill handle at the bottom-right of the cell to apply this formula down your column.

Syntax Check: Ensure your sheet names are spelled correctly in the formula. A minor typo, such as typing 'worsheet1' instead of 'worksheet1', will cause the formula to fail.

Solution 2

Basic INDEX MATCH with LEFT and FIND (Without Error Handling)

If your data is perfectly clean and every lookup result is guaranteed to contain a parenthesis, you can use a simpler version of the formula.

Powerful Spreadsheet Tool

Master Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, lookup functions, and text manipulation tools like INDEX, MATCH, LEFT, and FIND to streamline your data processing tasks effortlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your data workbook in the Spreadsheet module.
2. Use the Function Wizard: Click on the Formulas tab and select 'Insert Function' to easily build nested functions step-by-step.
3. Evaluate Formula: If you encounter errors, use the 'Evaluate Formula' feature under the Formulas tab to debug your INDEX, MATCH, and FIND operations.

100% compatible with Microsoft Excel functions and syntax.Built-in formula error checking and evaluation tools.Free, lightweight, and fast performance for large datasets.

Frequently Asked Questions

Why does my combined formula return a #VALUE! error?

A #VALUE! error usually occurs if the FIND function cannot locate the opening parenthesis in the lookup text. Wrapping your entire formula in an IFERROR function handles this gracefully.

Can I extract the text inside the parentheses instead?

Yes. You can use a combination of MID and FIND functions instead of LEFT to extract text located specifically between the opening and closing parentheses.

Is it possible to use XLOOKUP instead of INDEX and MATCH?

Absolutely. If you are using a version of Excel or WPS Spreadsheet that supports XLOOKUP, you can replace the INDEX and MATCH portion of the formula to shorten and simplify it significantly.

Excel & Data Problems