How to Use Excel Formulas to List Values Based on Criteria?

Sharing is caring!

Are you working with a large Excel spreadsheet and need to filter and list values that meet specific criteria? Excel provides powerful formula functions that allow you to easily extract and list values based on one or more conditions.

Whether you’re analyzing sales data, managing a project schedule, or tracking employee information, being able to quickly list out specific subsets of your data is essential. Fortunately, Excel makes this easy with a variety of formulas and functions designed for filtering and looking up values based on criteria.

In this comprehensive guide, we’ll show you step-by-step how to use the most useful Excel formulas to list values based on criteria, with clear examples and walkthroughs.

Using the FILTER Function to List Values Based on Criteria

If you’re using Excel 2019, Excel 2021, or Microsoft 365, the FILTER function is the most straightforward and flexible way to list values based on criteria. The FILTER function allows you to quickly extract rows or columns from a range that meet one or more conditions you specify.

FILTER Function Syntax

The syntax for the FILTER function is:

=FILTER(array, include, [if_empty])

• array: The range of cells you want to filter
• include: A boolean array (a range that evaluates to TRUE/FALSE values) that specifies which rows/columns to include in the output
• if_empty: [Optional] The value to return if no rows/columns match the criteria. If omitted, FILTER will return an empty array

FILTER Function Example

Let’s walk through a detailed example to see the FILTER function in action. Say you have an employee table in the range A1:D10, with columns for Name, Department, Title, and Salary:

To list only the employees in the Marketing department, you can use this FILTER formula:

=FILTER(A2:D10, B2:B10="Marketing", "No matching employees")  

Here’s how it works:

1. A2:D10 is the array argument, specifying the entire Employee table range
2. B2:B10="Marketing" is the include argument. It compares each value in the Department column (B2:B10) to “Marketing” and returns an array of TRUE/FALSE values
3. “No matching employees” is the optional if_empty argument, specifying what to return if no employees match the Marketing criteria

The FILTER function will return:

You can easily update the formula to filter for a different department just by changing “Marketing” to “Sales” or “Engineering”.

The real power of FILTER comes into play when you need to filter by multiple conditions. For example, to list only the Engineering employees with a salary greater than $100,000, you can use this formula:

=FILTER(A2:D10, (B2:B10="Engineering")*(D2:D10>100000), "No matching employees")

The include argument now uses boolean logic to check for rows where the Department is “Engineering” AND the Salary is greater than 100,000. The * operator performs an AND operation on the two arrays of TRUE/FALSE values.

You can add even more criteria by including additional logical expressions, such as:

(B2:B10="Engineering")*(D2:D10>100000)*(C2:C10="Senior Engineer")  

This would further filter the results to only Senior Engineers in the Engineering department with a salary over $100,000.

By nesting logical functions like AND, OR, NOT, etc. inside the FILTER criteria, you can build complex, highly-specific filters to extract just the data you need.

One nice feature of FILTER is that it automatically spills the results into the neighboring rows/columns, adjusting dynamically as the data changes. If you prefer to contain the output in a single cell, you can wrap the FILTER function inside TEXTJOIN to join the results into a comma-separated string.

Using INDEX and MATCH to Return Values Based on Criteria

If you’re using an earlier version of Excel that doesn’t include the FILTER function, you can achieve similar results by combining the INDEX and MATCH functions.

This approach is a bit more complex than FILTER, but it’s a powerful way to look up and return values that match specified criteria.

INDEX and MATCH Function Syntax

The INDEX function returns the value at a given position in a range or array. Its syntax is:

=INDEX(array, row_num, [column_num])  

• array: The range of cells or array to return a value from
• row_num: The row position in array to return a value from
• column_num: [Optional] The column position in array to return a value from. If omitted, INDEX treats array as a single column

The MATCH function searches for a specified value in a range and returns its relative position. Its syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value to search for
• lookup_array: The range to search in
• match_type: [Optional] The type of match to perform:
• 0 (default): Exact match
• -1: Exact match or next smallest value
• 1: Exact match or next largest value

By combining INDEX and MATCH, you can search for a value that matches specific criteria and return a corresponding value from the same row.

INDEX and MATCH Example

Let’s use the INDEX and MATCH functions to list employee names from the Marketing department using the same employee table example as before.

The formula to extract all names from the Marketing department is:

=INDEX($A$2:$A$10, MATCH("Marketing", $B$2:$B$10, 0))

Here’s a step-by-step breakdown:

1. MATCH("Marketing", $B$2:$B$10, 0) searches the Department column (B2:B10) for cells with the exact value “Marketing”. It returns the position of the first match.
2. INDEX($A$2:$A$10, ...) uses the row number returned by MATCH to retrieve the corresponding name value from column A.
3. The $ in the range references make them absolute, so they won’t change when the formula is copied down to extract the remaining matching names.

Note that this version of the formula only returns the first matching name. To list all matching names, you need to use an array formula by wrapping the INDEX and MATCH functions inside the SMALL function to return each matching row position in turn.

The full array formula (entered by pressing Ctrl+Shift+Enter) is:

=IFERROR(INDEX($A$2:$A$10, SMALL(IF($B$2:$B$10="Marketing", ROW($B$2:$B$10)-ROW($B$2)+1), ROW(A1))),"")

This more advanced formula uses the following logic:

1. IF($B$2:$B$10="Marketing", ...) returns an array of row numbers for Department values matching “Marketing”, or FALSE otherwise
2. SMALL(..., ROW(A1)) returns the nth smallest matching row number based on the current row
3. INDEX($A$2:$A$10, ...) returns the name at each matching row number
4. IFERROR(...) handles cases where there are no more matching names and returns a blank “” instead of an error

You would copy this formula down until it returns blank cells, to extract all matching names.

To look up values based on multiple criteria using INDEX and MATCH, you can modify the lookup array and use boolean logic like this:

=INDEX($A$2:$A$10, MATCH(1, ($B$2:$B$10="Engineering")*($D$2:$D$10>100000),0))

This searches for rows where the Department is “Engineering” AND the Salary is greater than 100,000, and returns the name from the first matching row.

While this approach requires a more complex setup than FILTER, especially for extracting all matches, it provides a flexible way to look up and return values based on criteria in earlier versions of Excel.

Using XLOOKUP or VLOOKUP to Extract Values Based on Criteria

The XLOOKUP and VLOOKUP functions provide another option to search for and extract values from an Excel table based on one or more criteria.

XLOOKUP is a newer function introduced in Excel 2021, while VLOOKUP has been part of Excel for a long time. Both can look up and return values based on criteria, but XLOOKUP includes several enhancements and is generally recommended if available.

XLOOKUP and VLOOKUP Syntax

XLOOKUP searches for a value vertically or horizontally in a table and returns a corresponding item from the same row. Its full syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value to search for
• lookup_array: The range or array to search in
• return_array: The range or array to return a value from
• if_not_found: [Optional] The value to return if lookup_value is not found. Defaults to #N/A
• match_mode: [Optional] Specifies the match type: 0 = exact match (default), -1 = exact match or next smaller item, 1 = exact match or next larger item, 2 = wildcard match
• search_mode: [Optional] Specifies the search mode: 1 = search first to last (default), -1 = search last to first, 2 = binary search (for sorted data)

VLOOKUP searches vertically in the leftmost column of a table and returns a value from the same row in a specified column. Its syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])  

• lookup_value: The value to search for
• table_array: The table of data to search in
• col_index_num: The column number (starting from 1) in table_array to return a value from
• range_lookup: [Optional] TRUE = approximate match (default), FALSE = exact match

XLOOKUP and VLOOKUP Example

Continuing with the employee table example, you can use XLOOKUP to look up an employee’s salary based on their name like this:

=XLOOKUP("Sarah", $A$2:$A$10, $D$2:$D$10, "Not found", 0, 1)

Here’s what each argument does:

1. “Sarah” is the lookup_value to search for
2. $A$2:$A$10 is the lookup_array, the range with employee names to search in
3. $D$2:$D$10 is the return_array, the range with salaries to return a value from
4. “Not found” is the if_not_found value to return if “Sarah” doesn’t exist in the name list
5. 0 sets the match_mode to exact match only
6. 1 sets the search_mode to search top-down (the default)

To do the same lookup with VLOOKUP, use:

=VLOOKUP("Sarah", $A$2:$D$10, 4, FALSE)  

• “Sarah” is the lookup_value
• $A$2:$D$10 is the full table_array range (name to salary)
• 4 is the col_index_num for the Salary column (column D)
• FALSE sets the range_lookup to exact match

Both formulas search the name column for “Sarah” and return her corresponding salary value from column D.

Note that XLOOKUP can look in one range and return a value from a different range, while VLOOKUP requires the lookup column to be the leftmost in the table, and uses a column index to specify the return column.

To look up values based on multiple criteria, you can feed XLOOKUP or VLOOKUP a value returned by the FILTER function. For example:

=XLOOKUP("Tom", FILTER($A$2:$A$10, ($B$2:$B$10="Marketing")*($C$2:$C$10="Manager")), FILTER($D$2:$D$10, ($B$2:$B$10="Marketing")*($C$2:$C$10="Manager")), "Not found")

This searches for “Tom” in a filtered list of names where the Department is “Marketing” AND the Title is “Manager”, and returns Tom’s corresponding salary.

Tips for Using Excel Formulas to List Values Based on Criteria

Here are a few best practices and tips to keep in mind when using formulas to list values based on criteria in Excel:

• Make sure your criteria ranges are the same size as the range you want to extract values from, to avoid formula errors
• Use absolute/mixed cell references (e.g. $A$2:$A$10) for your lookup ranges if you want to copy the formula to other rows/columns and have them reference the same range
• If you get a #SPILL! error when using the FILTER function, double-check that there are enough empty cells to the right/below the formula cell to fit the full output
• Test your criteria carefully and check that your logic returns the expected TRUE/FALSE values, especially when combining multiple conditions
• Use IFERROR to handle cases where no values match the criteria and provide a custom message or fallback value
• Consider using named ranges or Excel tables to make your data easier to reference and update in formulas

Final Thoughts

Extracting a list of values that meet specific conditions is a common task when working with Excel data. As we’ve seen, Excel provides several powerful formulas that make it easy to filter, look up, and return values based on one or more criteria.

The FILTER function is the most flexible option and best choice in newer versions of Excel, allowing you to extract full rows or columns of data that match any number of criteria. You can think of it as a formula-based alternative to Excel’s Autofilter feature.

For earlier versions of Excel, you can combine the INDEX and MATCH functions to perform criteria-based lookups and return corresponding values from a table. It takes a bit more setup than FILTER but is a reliable and versatile approach.

Finally, the XLOOKUP and VLOOKUP functions are designed to search for a specific value in a range or table and return a corresponding value from the same row. By combining them with FILTER, you can look up values based on multiple criteria.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.