How to Find Minimum Value Matching Specific Text Using Excel Formulas
Question details
The user needs an Excel formula to match a specific character extracted from a text string and return the smallest corresponding value from a dataset.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up the minimum numerical value from a table where a condition must match a partial text string, such as the second character of a two-letter code.
- Observed behavior
- Requires a reliable method to dynamically extract a text character and use it as criteria to fetch the lowest matching numerical value from a designated data range.
Ensure your dataset is organized with the numeric values in one column and the corresponding text criteria in another. Verify that your version of Excel or WPS Office supports the MINIFS function, which is available in newer versions.
Use MINIFS Combined with the RIGHT Function
Combine the MINIFS function with the RIGHT function to evaluate a specific character from a text string and return the lowest matching value.
The MINIFS function is ideal for finding the smallest value in a range based on one or more criteria. By nesting a text-extraction function like RIGHT inside the criteria argument, you can dynamically match against specific characters without needing helper columns.
Identify the cell containing your source text (e.g., A1 containing 'AB'). Ensure your lookup table has values in one column (e.g., A10:A15) and the letters to match in another (e.g., B10:B15).
Select an empty cell where you want the minimum value to be displayed and begin by typing =MINIFS(.
Select the range with the numbers you want to evaluate, followed by the criteria range. For example, type $A$10:$A$15, $B$10:$B$15, to lock the ranges.
Finish the formula by extracting the required character using the RIGHT function. Type RIGHT(A1)) and press Enter. The complete formula will be =MINIFS($A$10:$A$15,$B$10:$B$15,RIGHT(A1)).
Easily Extract and Match Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like MINIFS, RIGHT, and LEFT, allowing you to seamlessly look up minimum values matching specific text. It offers a powerful and cost-effective environment for complex data analysis.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the workbook containing your lookup dataset.
- 2. Apply the MINIFS formula: Click on your target cell and enter the formula =MINIFS(value_range, criteria_range, RIGHT(target_cell)).
- 3. Calculate and analyze: Press Enter to instantly retrieve the minimum matching value, and drag the fill handle to apply the formula across other rows if needed.
Frequently Asked Questions
What if my version of Excel doesn't support the MINIFS function?
If you are using an older version of Excel (2016 or earlier), you can use an array formula combining MIN and IF instead: =MIN(IF(B10:B15=RIGHT(A1), A10:A15)). Remember to press Ctrl+Shift+Enter to calculate it properly.
How do I find the maximum value matching text instead?
Simply replace the MINIFS function with the MAXIFS function in your formula. The syntax remains exactly the same: =MAXIFS($A$10:$A$15, $B$10:$B$15, RIGHT(A1)).
Why is my MINIFS formula returning zero or an error?
This usually happens if the criteria range and the min range are not exactly the same size. It can also occur if the RIGHT function extracts a text string but the lookup column contains strictly numeric values. Ensure your data types match and the range arrays align.
Can I match text using wildcard characters with MINIFS?
Yes, MINIFS supports wildcards like the asterisk (*) for multiple characters and the question mark (?) for single characters. For example, using "*"&RIGHT(A1) as your criteria will match any text ending with the extracted character.
