Function Problems

How to Fix an Excel INDEX MATCH Formula Based on a Code Prefix

Amos GikundaAmos Gikunda Nov 13, 2026 869 views

Question details

The user needs the correct Excel formula to return the email or region from column B when the first two characters of the code in E12 match a two-character value in column A.

How to Fix an Excel INDEX MATCH Formula Based on a Code Prefix
Product
WPS Office Spreadsheets / Microsoft Excel
Device & OS
not provided
Scenario
The workbook contains a lookup table in columns A and B; the user enters a code in E12 and wants the formula to match only the code prefix against column A.
Observed behavior
The current INDEX MATCH formula is missing the exact-match setup, so it does not reliably return the expected email or region from column B.
Before you start

Before editing the formula, check that column A contains text values with the same two-character prefix format and that E12 contains at least two characters. Also confirm the lookup range and return range cover the same rows.

Solution 1Recommended

Use LEFT with MATCH Exact Match to Fix the INDEX MATCH Formula

Use this formula when the lookup list in column A contains two-character text codes and you need an exact prefix match instead of an approximate match.

The MATCH function must use 0 as the third argument to force an exact match. LEFT(E12,2) extracts the two-character prefix from the code in E12, and INDEX returns the corresponding value from column B.

1
Select the formula cell

Click the cell where you want the email or region result to appear.

2
Enter the INDEX MATCH formula

Type =INDEX($B$3:$B$38,MATCH(LEFT(E12,2),$A$3:$A$38,0)) into the formula bar.

3
Press Enter

Press Enter to apply the formula. The cell returns the email or region from column B whose column A value matches the first two characters of E12.

Use LEFT with MATCH Exact Match to Fix the INDEX MATCH Formula
Exact match configured: The 0 argument in MATCH ensures an exact text match, so similar prefixes are not matched approximately.

Fix the INDEX MATCH Prefix Lookup in WPS Office Spreadsheets

WPS Office Spreadsheets supports INDEX, MATCH, LEFT, and XLOOKUP, so you can apply the exact-match prefix formula directly in your workbook. Open the file, enter the formula, and get the email or region from column B without changing your existing Excel data.

  1. 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel file that contains the lookup table and code cell E12.
  2. 2. Select the formula cell: Click the cell where you want the email or region result to appear.
  3. 3. Enter the exact-match formula: Type =INDEX($B$3:$B$38,MATCH(LEFT(E12,2),$A$3:$A$38,0)) and press Enter.
  4. 4. Confirm the result: Change the code in E12 and verify that the correct email or region is returned from column B.
Supports INDEX, MATCH, LEFT, and XLOOKUP functions for prefix lookupsOpens and saves .xlsx, .xls, and .csv Microsoft Excel files without conversion issuesFree, lightweight, and designed with a familiar Office ribbon interfaceLets you edit existing Excel workbooks directly while preserving formulas and formatting
microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return #N/A even when the prefix exists?

The MATCH function is likely using approximate match because the third argument is omitted or not set to 0. Use MATCH(LEFT(E12,2),$A$3:$A$38,0) and make sure the values in column A are text without extra spaces.

Can I use this prefix lookup formula in WPS Office Spreadsheets?

Yes. WPS Office Spreadsheets supports INDEX, MATCH, and LEFT. Enter the formula normally and press Enter; no special array entry is needed for the LEFT and MATCH version.

How can I return a blank cell instead of an error when no code matches?

Wrap the formula with IFERROR, for example =IFERROR(INDEX($B$3:$B$38,MATCH(LEFT(E12,2),$A$3:$A$38,0)),""), or use XLOOKUP with an empty string as the fourth argument if your version supports it.

What if E12 contains a number instead of text?

LEFT works on text values. Convert the number to text with =LEFT(TEXT(E12,"@"),2) or format E12 as text, then use that value inside MATCH.