6 Excel Functions for Quickly Finding Data

When working with a large data set, you would be more productive if you knew how to quickly find what you need. Fortunately, Excel has several features that can help with this.

1 FIND

The FIND function in Excel returns the numerical position of a specified character or string (sequence of characters) within a larger string. It is useful for precise text extraction, manipulation and conditional formatting.

The syntax of the FIND function is:

FIND(search_text, text_to_search_in, [start_position]) 

Here, searched_text is the character or substring you want to find, and text_to_search_in is the larger text string you want to search for. The start_position parameter specifies where the function should start searching in the string.

Here’s an example of what the FIND function would look like in action:

=FIND("12345", "Order # 12345 - Completed") 

The above formula will return 9because the substring 12345 starts at this position.

The FIND function is case sensitive. It is case sensitive. For example, if you were looking for a letter AND in the substring applesyou will get an error.

If you want to find something while ignoring case sensitivity, use the SEARCH function instead. It has a similar syntax to the FIND function.

=SEARCH(search_text, text_to_search_in, [start_position]) 

2 THIRTY

The SORT function can help if you need to arrange data in a range in ascending or descending order to make it easier to find specific data in an Excel worksheet.

The syntax of the SORT function is:

SORT(range_to_sort, [sort_index], [sort_order], [sort_by]) 

The range_to_sort parameter is the range you want to sort. Use it sort_index parameter specifying which column or row number to sort by in the range (default is 1).

The sort_order parameter specifies the sort order of the list where 1 is ascending and -1 is descending (default is ascending). AND sort_by specifies whether to sort by rows (FALSE), which is the default setting, or columns (FAITHFUL).

We will sort the range in the screenshot below based on the second column and in descending order.

This is how the formula would look in Excel:

=SORT(A2:B6, 2, -1) 

The range should now be sorted in descending order.

3 FILTER

The FILTER function evaluates a range of data based on a condition and returns only the rows and columns that meet the condition.

The syntax of the FILTER function is:

FILTER(range_to_filter, condition, [value_if_empty]) 

The range_to_filter parameter is the field or range of cells you want to filter. The state parameter is a criterion that determines what is to be returned in the filtered result. The value_if_empty parameter specifies what to return if nothing matches the condition.

We are filtering the range in the screenshot below to show only the employees in the Sale department.

This is what the formula looks like in action:

=FILTER(A2:C6, B2:B6 = "Sales") 

You should now only see rows and columns that match the criteria you entered.

4 INDEX

If you want to get the value of a specific cell in a data range, you can use the INDEX function. Just enter the row and column where it is located.

The syntax of the INDEX function is:

INDEX(range_to_search, row_to_search_in, [column_to_search_in]) 

The range_to_search parameter is the range from which you retrieve the value. The row_to_search_in and column_to_search_in are the row and column numbers where the value is in the range (think of them as coordinates).

For example, in the screenshot below, we load a file Score (C4) that Alice got in her English test.

The formula looks like this:

=INDEX(A1:C4, 4, 3) 

This pattern will return 88 because that’s what Alice got in her English test.

5 WRESTLE

The MATCH function in Excel searches a range of data for a specified value and then returns its relative position. You can then use the returned value with functions like INDEX to dynamically retrieve and manipulate data.

The syntax of the MATCH function is:

MATCH(value_to_search, range_to_search_in, [match_type]) 

Here, value_to_search is the value you want to find a range_to_search_in is the range where you are looking for it.

The match_type parameter specifies the type of match to use. Here are the types you can use:

In our example, we will use the MATCH function to find the position Carol in scope. We also want it to be an exact match.

This is what the function would look like in Excel:

=MATCH("Carol", A2:A6, 0) 

After executing the above formula, the function will return 3 from an exact match Carol is the third item in the assortment.

6 XLOOKUP

You use the XLOOKUP function to specify a value to look up in a range and then extract the corresponding value from another range. Unlike HLOOKUP and VLOOKUP, XLOOKUP allows you to search in any direction, which makes searching for data in a worksheet more flexible.

The syntax of the XLOOKUP function is:

XLOOKUP(value_to_look_up, range_to_check, range_to_return, [if_value_not_found], [match_type], [search_mode]) 

In this syntax it is value_to_look_up is the value you are looking for range_to_check is the range from which the desired value will be extracted, a range_to_return is where the matching lookup value will come from. You only need to specify these three parameters for the function to work, so we will only focus on these.

Let’s clarify this with an example based on the screenshot below. We want to look up Banana (A2) and returns its color from the corresponding column (B).

This is how the formula will look in Excel:

=XLOOKUP("Banana", A:A, B:B) 

Running it will return it Yellow (B2), the corresponding value based on the value lookup Banana.

Mastering these six Excel functions—FIND, SORT, FILTER, INDEX, MATCH, and XLOOKUP—will help you quickly find, organize, and extract information from large data sets. This can greatly improve your Excel productivity and ability to effectively analyze data.