How to Match an Exact Product Code in a URL and Return the URL in WPS Office Spreadsheets
Question details
The user needs to search more than 5,000 image URLs for an exact product code value and return the matching image link without false positives from similar codes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Product codes are stored in cells and image URLs are stored in an ImageLinks column. Similar product codes create false matches when lookup and text functions perform partial matching.
- Observed behavior
- A simple partial text search returns false positives because one product code can be contained within another code or URL segment.
Before writing the formula, confirm where the product code appears in the URL path and which characters surround it, such as slashes, hyphens, or file extensions. Add a helper column if the code is not already isolated so the lookup compares exact values instead of partial text.
Extract the product code into a helper column and return the image URL with XLOOKUP
Use this method when the URL structure is consistent. A helper column makes the lookup value exact and prevents partial-match false positives across large URL lists.
Separating the product code from the surrounding URL text is the most reliable way to compare only the code. The helper column does not change the original ImageLinks data.
In WPS Office Spreadsheets, open the sheet that contains the ImageLinks column. Click the first empty cell to its right, type ExtractedProductCode as the column heading, select the data cells in that column, click the Name Box, type ExtractedProductCode, and press Enter.
In the first data row of the helper column, enter =TEXTBEFORE(TEXTAFTER(B2,"/",-1),".") and press Enter. Replace B2 with the first cell of the ImageLinks column. This extracts the last URL segment before the file extension.
Select the helper cell and double-click the fill handle or drag it down to apply the formula to all 5,000+ URL rows.
In the result cell, enter =XLOOKUP(A2,ExtractedProductCode,ImageLinks,"Not found",0) and press Enter. A2 is the product code, ExtractedProductCode is the helper column, and ImageLinks is the original URL column.
Check several known codes, especially similar codes that previously caused false matches, to confirm only the exact image URL is returned.
Use a delimiter-aware wildcard lookup without a helper column
Use this method when you cannot add a helper column and the URL delimiter around the product code is known.
Match Product Code URLs Exactly in WPS Office Spreadsheets
Open your existing workbook in WPS Office Spreadsheets and use delimiter-aware extraction with XLOOKUP to return the correct image URL for any product code.
- 1. Open the workbook in WPS Office Spreadsheets: Install WPS Office, open the workbook containing the ImageLinks column, and keep the product-code cells available.
- 2. Create the exact-match helper column: Use the recommended method to add an ExtractedProductCode helper column and isolate the product code from each URL.
- 3. Return the URL with XLOOKUP: In the result cell, enter =XLOOKUP(A2,ExtractedProductCode,ImageLinks,"Not found",0) and press Enter.
- 4. Apply the lookup to all product codes: Drag the result formula down to return matching image links for all product codes in the list.
Frequently Asked Questions
Why does a simple XLOOKUP or VLOOKUP return the wrong image URL for similar product codes?
A partial or wildcard search can match a shorter code inside a longer code. Use a helper column with delimiter extraction or anchor the search with delimiters such as a slash before the code and a file extension after it.
Which WPS Spreadsheets functions can extract the product code from an image URL?
If your version supports them, use TEXTBEFORE and TEXTAFTER. If not, use a formula based on FIND, SUBSTITUTE, RIGHT, and LEFT with the known delimiter that surrounds the product code.
How do I apply the extraction formula to all 5,000 URL rows?
Select the first helper cell and double-click the fill handle or drag it down to the last URL row. WPS Office Spreadsheets then applies the formula to the selected range.
What if the product code is not the last segment of the URL?
Identify the delimiter before and after the code, then use the same delimiter-aware method with TEXTBEFORE and TEXTAFTER or a helper column to isolate that segment before running XLOOKUP.
