How To Search In MS Excel In Simple Steps

Learn how to search in MS Excel in this article where you will know many ways to do it.

MS Excel, sometimes known as Microsoft Excel, is a software program that is part of the Microsoft Office Suite. Users of Microsoft Excel can format, arrange, compute, and filter data in a spreadsheet.

As one works on Microsoft Excel, we usually add in big data. The spreadsheet becomes huge and then it becomes difficult to find something on the sheet. Well, not exactly. Searching is one of the easiest things that you can do in Excel. 

Ctrl + F is one of the most common ways to do so but let us learn a few more sophisticated ways to search in Excel.

How to Search in MS Excel

The VLOOKUP Function

This function lets you find a specific value within a column and get values from the corresponding row in adjoining columns. You might use this in two scenarios (1) looking up an employee’s last name by their employee number, or (2) finding a phone number by specifying the last name.

Here’s the syntax of the function:

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

• [lookup_value] is the piece of information that you already have. For example, if you need to know what state a city is in, it would be the name of the city.
• [col_index_num] is the number of the column that contains the return value.
• [table_array] lets you specify the cells in which the function will look for the lookup and return values. When selecting your range, be sure that the first column included in your array is the one that will include your lookup value!
• [range_lookup] is an optional argument, and takes 1 or 0, though you could also enter TRUE or FALSE. If you enter 1 or omit this argument, the function looks for an approximate value, but we’ve found this to be hit-or-miss. In the example below, a VLOOKUP looking for a score of 100 returns 90. Looking for a lower value, for example 88, returned an error.

Let’s take one example. This spreadsheet contains student names and scores for four different tests. If you want to find score #4 for the student with the last name “Davidson.” VLOOKUP makes it easy.

Here’s how to find out:

=VLOOKUP(“Davidson

Because the fourth score is the fifth column over from the last name, 5 is the column index argument. 

Here’s the result:

It returned 79, which is score #4 of the student we queried.

Also Read
How to compare two columns in Excel
How to remove blank rows in Excel
How to enable macros in Excel

The HLOOKUP Function

HLOOKUP function finds corresponding values in a different row and is best used when you have huge spreadsheets, or if you’re working with values that are organized by time.

Here’s the syntax of the function:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])

• [lookup_value] is the value that you know and want to find a corresponding value for.
• [table_array] is the cells in which you want to search.
• [row_index_num] specifies the row that the return value will come from.
• [range_lookup] is the same as in VLOOKUP, leave it blank to get the nearest value when possible, or enter 0 to only look for exact matches.

In the same spreadsheet as before, you can use HLOOKUP to find the score for a specific row. Here’s how we’ll do it:

=HLOOKUP(“Score 4”

As you can see in the image below, the score is returned:

The student in row 6, Thomas Davidson, had a score of 68 on his fourth test.

The FIND Function

Use Excel’s FIND function to identify the position of a string of text within another string of text.

For example, you wanted to find the first occurrence of the letter “x” in the phrase “The brown fox jumped over the fence.” This would be the function:

=FIND(“x”, “The brown fox jumped over the fence”)

The resulting number represents the position of the queried string. If you’re looking for a multi-character string, let’s say we queried for “fox,” the result would indicate the position of the query’s first character; in this case 11.

Also Read
How to remove duplicates in Excel
How to remove scroll lock in Excel
How to remove space in Excel

The XLOOKUP Function

This is a new function designed to replace VLOOKUP. Like VLOOKUP, you can use it to find things in a table or range by searching for a known value. It differs from VLOOKUP in that it lets you look up values located in columns to the left or right of the queried value; with VLOOKUP you can only ever find data to the right of the queried column.

Here’s the syntax of the function:

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

• [lookup_value] is the value you’re searching for; i.e. your query.
• [lookup_array] is the array or range to search.
• [return_array] is the array or range to return. This is the first difference from VLOOKUP.
• [if_not_found] is an optional argument that returns a message of your choice if no match is found.
• [match_mode] is another optional argument that lets you find exact matches (0), the next smaller item (-1), the next larger item (1), or a wildcard match (2).
• [search_mode] is optional and lets you control in which order to search. The default (1) starts the search at the first item. You can also start at the last item (-1), and perform a binary search that depends on the lookup_array being sorted in ascending (2) or descending (-2) order.

Let’s take our VLOOKUP example and reverse the search order. Now you can find the score of the second student called Davidson. Here’s the formula:

=XLOOKUP(G2,B2:B25,F2:F25,,,-1)

Note that we’re pulling the name from column G2, rather than writing it into the formula. Below is what it looks like.

This time, the formula returned Thomas Davidson’s score, rather than that of Aidan Davidson.

conclusion

Microsoft Excel is a software program created by Microsoft that uses spreadsheets to organize numbers and data with formulas and functions. MS Excel can be used by ordinary people for simple tasks like average determination , calculating subtraction, cell splitting, using pivot tables and more.

EasySheet is a tech blog that features amazing Excel hacks, tips, and tricks. Our mission is to help beginners learn Excel and get answers to all of their questions using the EasySheet.

We are the best place to go for help with anything related to Microsoft Office, including Excel, Word, PowerPoint, Publisher, and more!