Excel Formula for an Exact Match and Partial Text Search
Question details
Return a value from column C when column A matches a lookup value and column B contains a specified substring.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs to look up a row using both an exact column A value and a partial text match in column B.
- Observed behavior
- The desired result is the column C value from the first row that meets both conditions.
Check that the lookup data is on Sheet2, with values in columns A, B, and C on corresponding rows. Place the lookup value in A1 on the sheet where you want the result, and choose an empty cell for the formula.
Combine INDEX, MATCH, and SEARCH for both conditions
Use this formula to return the first column C value whose row meets both lookup conditions.
The equality test compares the full value in column A, while SEARCH checks whether column B contains the specified text. Both comparisons are case-insensitive.
On the sheet containing the lookup value in A1, select an empty cell such as B1. Do not overwrite a cell in the lookup table.
In the selected cell, enter =INDEX('Sheet2'!$C$1:$C$3,MATCH(1,INDEX(('Sheet2'!$A$1:$A$3=A1)*ISNUMBER(SEARCH("zzz",'Sheet2'!$B$1:$B$3)),0),0)) and press Enter.
In the formula bar, replace "zzz" with the text to find, adjust A1 if your lookup value is elsewhere, and extend all three Sheet2 ranges to cover the same rows.

Find matching rows with WPS Spreadsheets
Use the same INDEX, MATCH, and SEARCH formula in WPS Spreadsheets to retrieve a value based on an exact column match and a partial text search.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Check the lookup data: On Sheet2, confirm that columns A, B, and C contain the matching values, searchable text, and return values. Enter the lookup value in A1 on the result sheet.
- 3. Enter the formula: Select an empty cell on the result sheet and enter =INDEX('Sheet2'!$C$1:$C$3,MATCH(1,INDEX(('Sheet2'!$A$1:$A$3=A1)*ISNUMBER(SEARCH("zzz",'Sheet2'!$B$1:$B$3)),0),0)).
- 4. Adjust and save: Replace "zzz" and the cell ranges with your own search text and data ranges, then use File > Save As to save the workbook.

Frequently Asked Questions
How can I search for text stored in a cell instead of typing it in the formula?
Replace "zzz" inside SEARCH with a cell reference, such as D1: SEARCH(D1,'Sheet2'!$B$1:$B$3). Enter the text you want to find in D1.
Does the formula distinguish uppercase from lowercase?
No. The column A equality test and SEARCH are both case-insensitive. To make the text search case-sensitive, replace SEARCH with FIND.
How do I display a message instead of #N/A?
Wrap the formula in IFNA, for example, =IFNA(your_formula,"No match"). Replace your_formula with the complete INDEX and MATCH formula.
What happens if more than one row meets both conditions?
MATCH finds the first qualifying row in the specified ranges, so INDEX returns that row's column C value.
Can SEARCH find literal asterisks or question marks?
SEARCH treats * and ? as wildcards. Prefix either character with a tilde to search for it literally, such as SEARCH("~*",B1) for an asterisk.




