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

TheFIND Function in Excelreturns the numerical position of a specified character or string (sequence of characters) within a larger string. It’s useful for precise text extraction, manipulation, and conditional formatting.

The results of the FIND function in Excel on Windows.

The syntax for the FIND function is:

Here,search_textis the character or substring you want to locate, andtext_to_search_inis the larger text string you want to search. Thestart_positionparameter specifies where you want the function to begin searching in the string.

Any parameter in square brackets is optional.

Here is an example of how the FIND function would look in action:

The above formula will return9, since the substring12345starts at that position.

A range in Excel showing names and test scores.

The FIND function is case-sensitive. It distinguishes between uppercase and lowercase letters. If, for instance, you searched for the letterAin the substringapples, you would 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.

The results of the SORT function in Excel on Windows.

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 your Excel sheet.

The syntax of the SORT function is:

Therange_to_sortparameter is the range you want to sort. Use thesort_indexparameter to specify which column or row number to sort by within the range (default is1).

Thesort_orderparameter specifies the order for sorting the list, where1is ascending and-1is descending (default is ascending). Andsort_byspecifies whether to sort by rows (FALSE), which is the default, or columns (TRUE).

A range in Excel showing employee names, departments they work in, and their salaries.

We are going to sort the range in the screenshot below based on the second column and in descending order.

Here is what the formula would look like in Excel:

The range should now be sorted in descending order.

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:

Therange_to_filterparameter is the array or range of cells you want to filter. Theconditionparameter is the criteria that determines what should be returned in the filtered result. Thevalue_if_emptyparameter specifies what should be returned if nothing satisfies the condition.

We will filter the range in the screenshot below to show only employees in theSalesdepartment.

The results of the FILTER function in Excel on Windows.

Here is what the formula looks like in action:

You should now only see the rows and columns that meet the specified criteria.

If you want to get the value of a particular cell in a data range, you can use the INDEX function. You just need to specify the row and column where it’s located.

The syntax of the INDEX function is:

Therange_to_searchparameter is the range from which you’ll retrieve the value. Therow_to_search_inandcolumn_to_search_inare the row and column numbers where the value is located within the range (think of them as coordinates).

For instance, in the screenshot below, we’ll retrieve theScore(C4) that Alice got in her English test.

Here is what the formula looks like:

This formula will return88since that is what Alice scored on her English test.

TheMATCH function in Excelsearches 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 retrieve and manipulate data dynamically.

The syntax of the MATCH function is:

Here,value_to_searchis the value you want to find andrange_to_search_inis the range where you are searching for it.

Thematch_typeparameter specifies the type of match to use. Here are the types you can use:

Match Type

Description

1 (default)

Return the largest value less than or equal tovalue_to_search

Return an exact match

Return the smallest value greater than or equal tovalue_to_search

For our example, we’ll use the MATCH function to find the position ofCarolin the range. We also want it to be an exact match.

Here is what the function would look like in Excel:

After running the formula above, the function will return3since the exact match ofCarolis the third item in the range.

With the XLOOKUP function, you specify a value to search for in a range and then extract the corresponding value from another range. Unlike theHLOOKUP functionandVLOOKUP function, XLOOKUP allows you to search in any direction, making looking up data in your sheet more flexible.

The syntax of the XLOOKUP function is:

In this syntax, thevalue_to_look_upis the value you are looking up,range_to_checkis the range where the value you want will be extracted, andrange_to_returnis where the corresponding value of the lookup value will come from. You only need to specify these three parameters for the function to work, so these are the only ones we will focus on.

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

Here is what the formula will look like in Excel:

Running this will returnYellow(B2), the corresponding value based on looking up the valueBanana.

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