Function Problems

How to Return a Specific Value Based on Text in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs an Excel formula that evaluates a text string in one column and returns a specific, pre-defined numeric value in another column (e.g., returning prices for 'Shirt', 'Pants', and 'Sweater').

Product: Excel
Device & OS: not provided
Scenario: Automatically populating numerical values, such as item prices or product codes, corresponding to specific text categories entered into a spreadsheet.
Observed behavior: Requires a logical formula to read text in column J and output the exact assigned number in column I without manual data entry.

Before you start

Ensure that the text entries in your source column are consistent and do not contain accidental leading or trailing spaces, as the formula requires an exact text match to work correctly.

Solution 1Recommended

Use the SWITCH Function (Recommended)

The SWITCH function is the cleanest and most efficient way to evaluate a single text value against a list of matching options and return the corresponding numeric result.

This function compares one value against a list of possibilities and returns a result corresponding to the first match. It avoids the complex and confusing syntax of multiple nested IF statements.

Select the target cell

Click on cell I2 (or the first cell in the column where you want the numeric value to appear).

Enter the SWITCH formula

Type the formula exactly as follows: =SWITCH(J2,"Shirt",49.25,"Pants",78.5,"Sweater",94.65,"") and press Enter.

Fill the formula down

Click on cell I2 again, hover over the small square in the bottom-right corner of the cell (the fill handle), and drag it down the column to apply the formula to the remaining rows.

Format as Currency

Select the entire column I, go to the Home tab on the top ribbon, and choose 'Currency' or 'Accounting' from the Number Format dropdown to display the values properly.

Default Value Included: The final "" at the end of the formula acts as a default value. If a cell in column J is blank or contains unlisted text, column I will remain blank instead of showing an error.

Solution 2

Use Nested IF Functions

If you are using an older spreadsheet version that does not support the SWITCH function, nested IF statements serve as a universal alternative.

Try WPS Spreadsheet

Use WPS Spreadsheet to Handle Complex Formulas Easily

WPS Office Spreadsheet fully supports advanced logical functions like SWITCH, IF, and VLOOKUP, making it incredibly easy to automate tasks like assigning numerical values based on text categories.

1. Open your data in WPS: Launch WPS Spreadsheet and open your document containing the text columns.
2. Insert the function: Select the output cell, go to the Formulas tab, and click Insert Function. You can search for SWITCH to get a guided interface.
3. Input the arguments: Enter your reference cell (e.g., J2) and define your text-to-number pairs ('Shirt' / 49.25), then click OK.
4. Drag to apply: Use the fill handle to apply your new automated calculation to the entire list seamlessly.

100% compatible with Microsoft Excel formulas (.xlsx)Fully supports the SWITCH function and modern array formulasFeatures a built-in formula evaluation tool to help you troubleshoot complex logicLightweight, fast, and free to download

microsoft office alternative - wps office

Frequently Asked Questions

What if my text entries have extra spaces and the formula won't recognize them?

Extra spaces will prevent an exact match. You can wrap the cell reference inside a TRIM function within your formula, like this: =SWITCH(TRIM(J2), "Shirt", 49.25...).

How do I return a default value for any text not specified in the formula?

In the SWITCH function, an odd number of arguments at the end determines the default result. In the formula =SWITCH(J2,"Shirt",49.25,""), the final "" acts as the default, returning a blank cell for unmatched text. You can change "" to a word like "Not Found" or a number like 0.

Is there a better way to do this if I have dozens of different text categories?

Yes. If you have more than 5 or 6 text items, using a lookup table is highly recommended. Create a separate table listing all text items and their corresponding values, then use a VLOOKUP or XLOOKUP formula to reference that table. This is easier to update than a long SWITCH formula.

Excel & Data Problems