How to Use Excel Formulas to Identify the Cheapest Item and Return 'BUY'
Question details
The user needs a spreadsheet formula to check a specific threshold, compare values to find the lowest one, and return the word 'BUY' next to the matching item.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Comparing multiple prices against a condition to automatically find and flag the cheapest qualifying option in a dataset.
- Observed behavior
- The user wants to dynamically output the text 'BUY' in a cell next to the lowest priced item that meets their criteria, and optionally highlight that row.
Ensure your dataset is organized with clear column headers (e.g., Oil Name, Price, Low Value Threshold) and that the price data contains pure numeric values without hidden text or currency symbols that might break calculation formulas.
Use a Combination of IF and MINIFS to Find and Mark the Lowest Price
This solution dynamically identifies the lowest value meeting your condition and outputs 'BUY' next to the corresponding item in your data table.
The MINIFS function is perfect for finding the lowest price that meets a specific threshold or condition. By wrapping this in an IF statement, you can compare each row's price to that minimum value. If it matches, the formula returns 'BUY'; otherwise, it remains blank.
Assume Column A is 'Oil Name', Column B is 'Price', and Column C is 'Status' (where you want 'BUY' to appear). Ensure your threshold is defined, for example, only considering oils under $50.
Click on cell C2 and enter the formula: =IF(B2=MINIFS($B$2:$B$100, $B$2:$B$100, "<50"), "BUY", ""). This checks if the price in B2 matches the lowest price in the range that is also under 50.
Press Enter, then drag the fill handle (the small square at the bottom-right of cell C2) down to apply this formula to the rest of the rows in Column C.
Select your entire data range (e.g., A2:C100). Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$C2="BUY" and click Format to choose a green fill color.
Identify Minimum Values and Automate Workflows in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions, MINIFS, XLOOKUP, and conditional formatting, allowing you to easily build criteria-based formulas to find the cheapest items for free.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the oil pricing data.
- 2. Input the comparison formula: Select the target cell in your 'Status' column and type =IF(B2=MINIFS($B$2:$B$100, $B$2:$B$100, "<50"), "BUY", "") to flag the lowest price under your threshold.
- 3. Highlight the 'BUY' cell automatically: Navigate to the Home tab, click 'Conditional Formatting', select 'Highlight Cells Rules', choose 'Equal To', and type 'BUY' to automatically color the winning cell.
- 4. Save your automated workbook: Click Save to retain your workbook in the standard .xlsx format, fully compatible with other spreadsheet software and colleagues.
Frequently Asked Questions
Why is my MINIFS function returning a #NAME? error?
The MINIFS function is only available in Microsoft 365, Excel 2019, or modern alternatives like WPS Spreadsheet. If you are using an older version (like Excel 2016), you must use an array formula combining MIN and IF, such as {=MIN(IF(range<50, range))}, entered with Ctrl+Shift+Enter.
How do I return the name of the cheapest oil instead of just 'BUY'?
You can use the XLOOKUP function. For example, =XLOOKUP(MINIFS(PriceRange, PriceRange, "<50"), PriceRange, OilNameRange) will directly fetch and display the name of the oil that has the lowest qualifying price.
What happens if two oils have the exact same lowest price?
If you use the IF and MINIFS combination approach, the formula will return 'BUY' for all items that share that exact minimum price. If you use XLOOKUP, it will only return the first match it finds in the dataset by default.
Can I check multiple threshold conditions at once?
Yes. The MINIFS function supports multiple criteria pairs. For instance, =MINIFS(PriceRange, CriteriaRange1, "Condition1", CriteriaRange2, "Condition2") allows you to find the lowest price only among items that meet all specified conditions.
