Formula Errors

Excel Formula to Return Prices for Small, Medium, and Large Sizes

Maira Mehtab Sep 22, 2026 868 views

Question details

The user requires an Excel formula that identifies 'small', 'medium', or 'large' text in a cell, outputs a corresponding price, and leaves empty or unrecognized cells blank for accurate sum calculations.

Product: Excel
Device & OS: not provided
Scenario: Automating price assignments based on text size descriptors while maintaining blank cells to ensure SUM functions work correctly at the end of a column.
Observed behavior: The goal is to automatically return specific values based on substring matches ('sma', 'med', 'lar') and return a blank string for empty or non-matching cells.

Before you start

Verify that your pricing rates are stored in a reliable reference sheet or cell range, such as a 'Rates' tab, so your formula can reference them without hardcoding values.

Solution 1Recommended

Use a Nested IF Formula with ISNUMBER and SEARCH

Combine the IF, ISNUMBER, and SEARCH functions to scan a cell for partial text matches and return the correct price, while ensuring blank cells output nothing.

The SEARCH function looks for partial string matches like 'sma', 'med', or 'lar' inside the target cell. Because SEARCH returns an error if text isn't found, wrapping it in ISNUMBER converts the result into a clean TRUE or FALSE, which the IF function can then evaluate.

To ensure seamless column totaling later on, the formula explicitly checks if the cell is completely empty (="") at the very beginning, returning a blank string if true.

Set up your reference prices

In a separate worksheet named 'Rates', enter your prices. For example, enter the small price in cell F2, medium in F3, and large in F4.

Select the target cell

Click on the cell where you want the calculated price to be displayed adjacent to your size data.

Input the nested formula

Type =IF(F119="","",IF(ISNUMBER(SEARCH("lar",F119)),Rates!$F$4,IF(ISNUMBER(SEARCH("med",F119)),Rates!$F$3,IF(ISNUMBER(SEARCH("sma",F119)),Rates!$F$2,"")))) and press the Enter key. Be sure to change F119 to your specific target cell.

Drag to apply to other rows

Hover over the bottom-right corner of the cell until the cursor turns into a cross, then click and drag downward to apply the pricing logic to the rest of your column.

Use Absolute References: Notice the use of dollar signs ($F$4) in the formula. This absolute referencing ensures that when you drag the formula down, it continues pointing to the exact cells where your prices are stored.

Advanced Formula Support

Easily Handle Complex Formulas with WPS Spreadsheet

WPS Spreadsheet offers powerful calculation capabilities and full support for advanced nested functions, making it simple to process pricing logic and analyze data without hassle.

1. Open your data file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your size and pricing data.
2. Insert the nested pricing formula: Select your target cell and paste the exact IF and SEARCH formula used in Excel.
3. Fill down the column: Double-click the fill handle on the cell's bottom-right corner to automatically calculate prices for all remaining rows.

100% compatible with Microsoft Excel formulas, including IF, ISNUMBER, and SEARCH.Lightweight architecture ensures fast calculations even across massive datasets.Provides intuitive formula assistance and syntax highlighting to prevent errors.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my nested formula return a #VALUE! error?

This error typically occurs if you use the SEARCH function without wrapping it in ISNUMBER. If SEARCH cannot find the text, it returns a #VALUE! error, which breaks the entire IF statement.

Can I hardcode the prices into the formula instead of using a 'Rates' sheet?

Yes. If your prices rarely change, you can replace the cell references (like Rates!$F$4) with actual numbers. For example, use =IF(F119="","",IF(ISNUMBER(SEARCH("lar",F119)),10.00,...)).

How do I modify this formula to look for exact matches only?

If you don't need partial text matching (like finding 'sma' inside 'small shirt'), you can skip ISNUMBER and SEARCH entirely. Use a simpler structure: =IF(F119="Large", Rates!$F$4, IF(F119="Medium", Rates!$F$3...)).

Excel & Data Problems