Function Problems

How to Use Excel IFS Formula for Multiple Matching Values and Cities

WPS Editor Oct 1, 2026 869 views

Question details

The user needs a formula to return a specific city name based on whether a cell matches one of several different community names.

Product: Excel
Device & OS: not provided
Scenario: Categorizing or mapping data where multiple different text values belong to the same category group, requiring a consolidated logical check.
Observed behavior: The user wants to output the correct city name when the target cell matches any community within a predefined array of names.

Before you start

Verify that your spreadsheet software supports the IFS function (available in Microsoft Excel 2019 or later, Microsoft 365, and WPS Office). Older versions will require nested IF statements instead.

Solution 1Recommended

Combine IFS and OR Functions with Array Constants

Use the IFS function alongside the OR function and array constants to check multiple text values concisely without writing repetitive IF statements.

Array constants enclosed in curly brackets allow the OR function to evaluate multiple text strings simultaneously against a single cell. This keeps your formula clean and easy to edit.

Select the target cell

Click on the cell in the adjacent column where you want the city name to be displayed.

Enter the IFS and OR formula

Type the following formula: =IFS(OR(C27={"Boykin Hills","Collins Cove","Night Harbor"}),"Chapin",OR(C27={"Bradford Meadows","Canopy of Oaks","Jackson Preserve","South Bridge","Stillpointe","The Cove"}),"Sumter",TRUE,"")

Adjust cell references and apply

Replace 'C27' with the actual cell reference containing your community name. Press Enter, then drag the fill handle down to apply the formula to the rest of your data.

Combine IFS and OR Functions with Array Constants

Using a Catch-all Condition: The TRUE, "" argument at the end of the IFS formula acts as a default catch-all, returning a blank cell instead of an error if the community name doesn't match any listed values.

Solution 2

Alternative: Use VLOOKUP with a Mapping Table

For datasets with dozens of communities, a VLOOKUP reference table is easier to manage and update than a very long IFS formula.

Master Data Analysis with WPS Office

Easily Map Multiple Data Values in WPS Spreadsheet

WPS Spreadsheet provides full support for advanced logical functions like IFS, OR, and array constants, making data categorization straightforward. It handles complex formulas perfectly while offering an intuitive interface for all your data tasks.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your community data.
2. Input the array formula: Click the cell for the result and type your combined =IFS(OR(...)) formula as required.
3. Fill the column: Hit Enter, then drag the bottom-right corner of the cell downwards to instantly categorize your remaining data.

100% format compatibility with Microsoft Excel files (.xlsx) and formulasSeamlessly executes advanced functions like IFS, XLOOKUP, and array formulasLightweight performance ensures quick calculations even with massive datasetsFree and easy to use for organizing, filtering, and mapping complex lists

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IFS formula return a #NAME? error?

A #NAME? error typically occurs if you are using an older version of Excel (prior to 2019) that does not support the IFS function, or if the function name was misspelled. If you are on an older version, you must use nested IF functions instead.

Can I use a range reference instead of typing names inside the curly brackets?

The curly brackets {} denote a hardcoded array constant. You cannot directly put a cell range like A1:A5 inside them. If you want to reference a range of cells, it's highly recommended to use a VLOOKUP or XLOOKUP formula combined with a reference table rather than the IFS and OR combination.

What happens if a cell doesn't match any condition in the IFS formula?

If no condition is met in an IFS formula, it will return an #N/A error. To prevent this, add TRUE as your final logical test, followed by the value you want to return (such as "" for a blank cell or "Not Found").

Excel & Data Problems