Searching a Microsoft Excel spreadsheet may seem easy. While Ctrl + F can help you find most things in a spreadsheet, you'll want to use more sophisticated tools to find and extract data based on specific values. We'll help you save tons of time with our list of advanced search functions.

Once you know how to search in Excel using lookup, it won’t matter how big your spreadsheets get, you'll always be able to find what you need!

1. The VLOOKUP Function

The VLOOKUP function lets you find a specific value within a column and extract values from the corresponding row in adjoining columns. Two examples where you might do this are (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.
  • [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!
  • [col_index_num] is the number of the column that contains the return 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.
Excel Vlookup Function 01

Let's take a look at how you might use this. This spreadsheet contains student names and scores for four different tests. Let's say you want to find score #4 for the student with the last name "Davidson." VLOOKUP makes it easy.

Here's the formula you'd use:

        =VLOOKUP("Davidson
    

Because the fourth score is the fifth column over from the last name we're looking for, 5 is the column index argument. Note that when you're looking for text, setting [range_lookup] to 0 is a good idea. Without it, you can get bad results.

Here's the result:

Excel Vlookup Function 02

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

Notes on VLOOKUP

A few things are good to remember when you're using VLOOKUP. Make sure that the first column in your range is the one that includes your lookup value. If it's not in the first column, the function will return incorrect results. If your columns are well organized, this shouldn't be a problem.

Also, keep in mind that VLOOKUP will only ever return one value. There was another student with the last name "Davidson," but VLOOKUP will only ever return results for the first entry, with no indication that there is more than one match.

Related: How to Do a VLOOKUP Search in Excel

2. The HLOOKUP Function

Where VLOOKUP finds corresponding values in another column, HLOOKUP finds corresponding values in a different row. Because it's usually easiest to scan through column headings until you find the right one and use a filter to find what you're looking for, HLOOKUP 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.

We'll use 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:

Excel Hloookup Function

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

Related How to calculate the weighted average in Excel

Notes on HLOOKUP

As with VLOOKUP, the lookup value needs to be in the first row of your table array. This is rarely an issue with HLOOKUP, as you'll usually be using a column title for a lookup value. HLOOKUP also only returns a single value.

3-4. The INDEX and MATCH Functions

INDEX and MATCH are two different functions, but when they're used together, they can make searching a large spreadsheet a lot faster. Both functions have drawbacks, but by combining them, we'll build on the strengths of both.

First, though, the syntax of both functions:

        =INDEX([array], [row_number], [column_number])
    
  • [array] is the array in which you'll be searching.
  • [row_number] and [column_number] can be used to narrow your search (we'll take a look at that in a moment).
        =MATCH([lookup_value], [lookup_array], [match_type])
    
  • [lookup_value] is a search term that can be a string or a number.
  • [lookup_array] is the array in which Microsoft Excel will look for the search term.
  • [match_type] is an optional argument that can be 1, 0, or -1. 1 will return the largest value that is smaller than or equal to your search term. 0 will only return your exact term, and -1 will return the smallest value that is greater than or equal to your search term.

It might not be clear how we're going to use these two functions together, so I'll lay it out here. MATCH takes a search term and returns a cell reference. In the image below, you can see that in a search for the last name "Davidson" in column B, MATCH returns 2.

Excel Match Function

INDEX, on the other hand, does the opposite: it takes a cell reference and returns the value in it. You can see here that, when told to return the second row of column B, INDEX returns "Davidson," the value from row 2.

Excel Index Function

What we're going to do is combine the two so that MATCH returns a cell reference and INDEX uses that reference to look up the value in a cell. Let's say you remember that there was a student whose last name was Townsend, and you want to see what this student's fourth score was. Here's the formula we'll use:

        =INDEX(F:F, MATCH("Townsend", B:B, 0))
    

You'll notice that the match type is set to 0 here. When you're looking for a string, that's what you'll want to use. Here's what we get when we run that function:

Excel Index Match Function

As you can see from the inset, Ralph Townsend scored a 68 in his fourth test, the number that appears when we run the function. This may not seem all that useful when you can just look a few columns over, but imagine how much time you'd save if you had to do it 50 times on a large database spreadsheet that contained several hundred columns!

5. The FIND Function

An article on finding something in Excel wouldn't be complete without the FIND function. But it might not be what you expect. You can use Excel's FIND function to identify the position of a string of text within another string of text.

Let's say, we wanted to find the first occurrence of the letter "x" in the phrase "The brown fox jumped over the fence." This would be our function:

        =FIND("x", "The brown fox jumped over the fence")
    
Excel Find function

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.

Notes on FIND

Like VLOOKUP, HLOOKUP, and other functions, FIND will only identify the first occurrence of a string. Note that FIND is case-sensitive. You can use it to FIND multiple characters. And while we used a letter in our example, it also works with numbers.

On its own, this function might not seem very useful, but it comes into its own when you start nesting functions. For example, you could use your FIND result to split a string of text at the position corresponding to the string identified with FIND.

We can't cover FIND without mentioning the SEARCH function. Well, it's essentially the same as FIND, except that it's not case-sensitive. It also allows wildcards, meaning you can search for matches that aren't exact.

Excel supports three wildcards:

  • Asterisk (*), which is a placeholder for any number of characters, including zero.
  • Question mark (?), which can replace any one character.
  • Tilde (~), which turns the wildcards "asterisk" and "question mark" into literal characters, meaning it cancels their wildcard function. You'd use it as ~* or ~?.

Related: Tips for Working With Text and Text Functions in Excel

6. The XLOOKUP Function

XLOOKUP 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), 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. This should let us 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 directly into the formula. Below is what it looks like.

Excel Xlookup Function

This time, the formula returned Thomas Davidson's score, rather than that of Aidan Davidson. But it still can't return more than one result.

Related: What Is the XLOOKUP Function in Excel and How to Use It?

Let the Excel Searches Begin

Microsoft Excel has a lot of extremely powerful functions for manipulating data, and the four listed above just scratch the surface. Learning how to use them will make your life much easier.