How to Use VLOOKUP with Numeric Ranges and Text Suffixes
Question details
The user needs to match complex alphanumeric codes against defined numeric ranges and specific text suffixes, and ignore any values outside the defined ranges without returning an error.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up values based on alphanumeric codes where the exact string is not listed in the lookup table, requiring a split between range-based numerical thresholds and text categories.
- Observed behavior
- Attempting to use the complete code as a literal text lookup fails because the full string is not explicitly listed, returning errors for valid items or failing to match ranges.
Ensure your lookup table containing the numeric ranges is sorted in ascending order by the lower boundary, as this is strictly required for approximate match lookups to function correctly.
Separate Logic for Numbers and Suffixes
Split the alphanumeric code into its numeric and text components, perform separate lookups, and combine the results while managing errors.
Since wildcards cannot handle numerical ranges and exact text matches simultaneously, the most reliable approach is to separate the data. By extracting the number, you can leverage an approximate match VLOOKUP to find the correct range bracket. A secondary exact match VLOOKUP handles the text suffix.
Wrapping this combined logic in an error-handling function ensures that any out-of-bounds inputs fail gracefully without displaying #N/A.
Use text functions to pull the number from the string. For example, if your code is '168AR' in cell A2, use `=VALUE(LEFT(A2, 3))` to extract '168' as a pure number.
Use the RIGHT function to separate the category code. In an adjacent cell, enter `=RIGHT(A2, 2)` to extract 'AR'.
Write a VLOOKUP formula for the extracted number using the TRUE parameter. This matches the number against the lower threshold of your range table: `=VLOOKUP(B2, NumericTableRange, 2, TRUE)`.
Write a second VLOOKUP formula for the extracted suffix using the FALSE parameter to require an exact match: `=VLOOKUP(C2, SuffixTableRange, 2, FALSE)`.
Combine both lookups using an ampersand (&) with a hyphen between them, and wrap the entire statement in IFERROR to skip invalid codes: `=IFERROR(VLOOKUP(B2, NumericTableRange, 2, TRUE) & "-" & VLOOKUP(C2, SuffixTableRange, 2, FALSE), "")`.
Process Complex VLOOKUP Scenarios Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text manipulation functions, approximate VLOOKUPs, and robust error handling. You can seamlessly perform complex alphanumeric matching and format conversions without needing complicated VBA scripts.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your unformatted alphanumeric codes.
- 2. Prepare helper columns: Insert two new columns next to your raw data to hold the extracted numbers and text suffixes.
- 3. Apply extraction formulas: Type `=VALUE(LEFT(A2, 3))` in the number column and `=RIGHT(A2, 2)` in the suffix column, then press Enter.
- 4. Enter the combined formula: In your result column, type the combined lookup formula: `=IFERROR(VLOOKUP(B2, NumberTable, 2, TRUE) & "-" & VLOOKUP(C2, SuffixTable, 2, FALSE), "")`.
- 5. Fill the data series: Click and drag the small square fill handle at the bottom-right of your formula cell down to apply this logic to all the codes in your sheet.
Frequently Asked Questions
Why does my approximate VLOOKUP return incorrect values?
For an approximate VLOOKUP (using TRUE as the last argument) to function properly, the first column of your lookup table must be sorted in ascending order. If it is unsorted, the formula will return incorrect matches or #N/A errors.
Can I use wildcard characters to match ranges in VLOOKUP?
No, wildcards like asterisks (*) or question marks (?) only work for exact text matches. To match a number within a range bracket, you must extract the numeric value and use an approximate match against the lower boundaries of your ranges.
How do I ignore codes that don't match any range or suffix?
You can wrap your main VLOOKUP formula inside the IFERROR function. For example, using =IFERROR([Your VLOOKUP Formula], "") will return a completely blank cell instead of displaying an error code if the lookup value falls outside your defined parameters.
How do I extract the numeric portion if the lengths vary?
If your alphanumeric codes have variable lengths (e.g., '16AR' and '1684AR'), relying on a fixed LEFT(A2, 3) will fail. You can instead isolate the text suffix using the RIGHT function, and use the SUBSTITUTE function or advanced LEN calculations to remove the suffix string, leaving only the variable-length number.
