How to Use LOOKUP and SEARCH for Reverse Partial Match in WPS Spreadsheets
Question details
The user needs to find a supplier name from a keyword contained inside an item description when the lookup text column and the result column are arranged in reverse order.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a supplier table with keywords and supplier names, and a description cell. They need to search for any keyword inside the description and return the associated supplier even though the keyword column is to the right of the supplier column.
- Observed behavior
- The goal is to retrieve the supplier associated with the last keyword found in the description without reordering the lookup and result columns.
Make sure your keyword list is in a consistent column and supplier names are in the adjacent result column, and identify the first description cell you want to evaluate.
Use LOOKUP with SEARCH to perform a reverse partial match
Use this formula when a supplier keyword appears inside a longer item description and the keyword column is located to the right of the supplier column you want to return.
LOOKUP ignores the #VALUE! errors returned by SEARCH for non-matching keywords and uses 9^9 as a lookup value larger than any possible text position. This makes LOOKUP return the supplier associated with the last keyword found in the description.
Open the workbook in WPS Spreadsheets and click the cell where the supplier name should appear, such as E2.
Type =LOOKUP(9^9,SEARCH(Table2[keyword],D2),Table2[Supplier]) and replace Table2[keyword] with the keyword column, D2 with the description cell, and Table2[Supplier] with the supplier column.
Press Enter, then drag the fill handle down to fill the formula for the rest of the description column.
Return the Supplier Using LOOKUP and SEARCH in WPS Spreadsheets
WPS Spreadsheets can handle this reverse partial match directly. Enter the LOOKUP/SEARCH formula in the result cell, point it to your keyword and supplier columns, and fill down to identify the supplier from every item description.
- 1. Open your workbook: Launch WPS Spreadsheets and open the workbook that contains the item descriptions and supplier keyword table.
- 2. Click the supplier result cell: Select the first cell where you want the matched supplier name to appear.
- 3. Enter the reverse partial match formula: Type =LOOKUP(9^9,SEARCH(Table2[keyword],D2),Table2[Supplier]), replacing the table references and D2 with your actual columns and first description cell.
- 4. Fill the formula down: Press Enter and drag the fill handle down the column to apply the formula to all item descriptions.
Frequently Asked Questions
Can I use this formula when my keyword column is to the right of the supplier column?
Yes. LOOKUP with SEARCH does not require the result column to be to the right; it returns values from the supplier column regardless of column order.
Why does the formula use 9^9 instead of a normal lookup value?
9^9 is a very large number, larger than any text position SEARCH can return. LOOKUP then matches the last numeric result from SEARCH and returns the corresponding supplier.
How do I change Table2[keyword] to a normal cell range in WPS Spreadsheets?
Replace Table2[keyword] with the keyword range, for example $B$2:$B$100, and replace Table2[Supplier] with the supplier range, such as $C$2:$C$100.
What happens if none of the keywords appear in the description?
SEARCH returns #VALUE! for every keyword and LOOKUP returns #N/A. You can wrap the formula in IFERROR(...,"Not found") to show a friendly message instead.
