How to Filter and Extract Data Based on Conditions in Excel

Buzz

Content

Understanding the IFERROR Function

Understanding the INDEX Function

Understanding the SMALL Function

How to Filter and Extract Data Based on Conditions in Excel

The Lookup function in Excel enables users to search based on conditions but only returns the first most accurate value. If you need to list all values that meet the condition and copy them to another data table or sheet... there isn't a direct tool that fulfills this requirement.

Consider the following example: You have a summary data table and need to extract all data based on a condition to another table.

So, how can you achieve this requirement? Today, Software Tips will guide you on combining available functions to list all values that meet the condition in Excel, using functions such as: IFERROR, INDEX, SMALL.

Understanding the IFERROR Function

Function structure: = IFERROR(value, value_if_error).

Where:

- IFERROR: is the function name used to detect error values and return the value you need.

- Value: the value we need to determine whether it's an error or not.

- Value_if_error: the value returned if the value is an error.

If the value of value is an error (#N/A, #REF, ...), the IFERROR function will return value_if_error, otherwise, it will return the value.

Understanding the INDEX Function

Syntax of the function: =INDEX(array, row_num, [column_num]).

Where:

- INDEX: is the function name used to retrieve the value at row n (row_num) in the data array array.

- Array: Data array. In this article, Software Tips only guides the INDEX function with an array that contains only one column.

- row_num: Selecting a row in the array to return a value from it.

Example of using the INDEX function:

Understanding the SMALL Function

Syntax of the function: =SMALL(array,k).

Where:

- SMALL: is the function name used to determine the kth smallest value.

- Array: Array or range of numeric data that you want to determine its kth smallest value.

- K: Position (from the smallest value) in the array or range of data to return.

Example of using the SMALL function:

How to Filter and Extract Data Based on Conditions in Excel

For the requirement presented by Software Tips above, you need to list 3 revenue items of the Hai Duong branch in column J.

In cell J4, enter the formula: =IFERROR(INDEX($G$1:$G$12,SMALL(IF($J$2=$B$3:$B$12,ROW($G$3:$G$12)),ROW(A1))),'') and press Ctrl + Shift + Enter. Then copy the formula for other rows in the column.

Here:

$G$1:$G$12: is the reference data array needed to retrieve values.
$J$2 is the location of the cell containing the value to be found.
$B$3:$B$12 is the data array to be searched.

So based on the formula above, you adjust the positions of the data arrays and corresponding data cells in your spreadsheet.

Note:

It's advisable to use absolute references for data arrays to avoid errors when copying formulas.
If your reference data range is long, it's recommended to copy the formula for the entire column or at least the same number of rows as the reference range for safety. Although I copied the formula up to cell J9, due to only 3 values satisfying the condition for the Hai Duong branch, only cells J3-J4 contain numerical data, while other cells remain blank, preserving the report's aesthetics.

The image below shows a different outcome if Software Tips changes the reference condition to: Ho Chi Minh branch.

Wishing you all success!

Frequently Asked Questions

How can I extract multiple values that meet a condition in Excel?

To extract multiple values based on a condition in Excel, you can use a combination of the IFERROR, INDEX, and SMALL functions. For example, you can enter a formula in the desired cell that checks for a specific condition and retrieves corresponding values from your data range.

What is the structure of the IFERROR function in Excel?

The structure of the IFERROR function is =IFERROR(value, value_if_error). It detects errors in a specified value and returns a defined alternative if an error is present, ensuring your formula outputs usable data instead of error messages.

What does the INDEX function do in Excel and how is it used?

The INDEX function retrieves the value at a specific position in a given data array. Its syntax is =INDEX(array, row_num, [column_num]), where 'array' refers to the data set, 'row_num' specifies the row to return a value from, and 'column_num' is optional.

How does the SMALL function work in Excel for data extraction?

The SMALL function returns the kth smallest value from a numeric data set. Its syntax is =SMALL(array, k), where 'array' is the range of data and 'k' indicates the position of the smallest value you want to retrieve, which helps in sorting or filtering data.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]