Most of the time, finding a cell in an Excel spreadsheet is pretty easy; if you can’t just scan through the rows and columns for it, you can use CTRL+F to search for it. But if you’re working with a really big spreadsheet, it can save a lot of time to use one of these four lookup functions. No matter the size of your Microsoft Excel document, they’ll bee much more efficient.
The VLOOKUP Function
This function allows you to specify a column and a value, and will return a value from the corresponding row of a different column (if that doesn’t make sense, it’ll become clear in a moment). Two examples where you might do this are looking up an employee’s last name by their employee number or finding a phone number by specifying a last name. Speaking of which, you can also separate first and last names in Excel.
Here’s the syntax of the function:
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
The [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! This is crucial. [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. If you enter 1 or omit this argument, the function looks for the value you entered or the next-lowest number. So in the image below, a VLOOKUP looking for an SAT score of 652 will return 646, as it’s the closest number in the list that’s less than 652, and [range_lookup] defaults to 1.
Let’s take a look at how we might use this. As I have in my articles on boolean operators in Microsoft Excel and advanced counting and adding, I’ll be using a spreadsheet that I generated with generatedata.com. It contains ID numbers, first and last names, city, state, and SAT scores. Let’s say I want to find the SAT score of a person with the last name “Winters.” VLOOKUP makes it easy. Here’s the formula we’d use:
=VLOOKUP("Winters", C2:F101, 4, 0)
Because the SAT scores are the fourth column over from the last name column, I’ve used 4 for 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 what Microsoft Excel gives us:
It returned 651, the SAT score belonging to the student named Kennedy Winters, who is in row 92 (displayed in the inset above). It would’ve taken a lot longer to scroll through looking for the name than it did to quickly type out the syntax!
VLOOKUP can also be quite useful if you’re using Microsoft Excel to do your taxes.
Notes on VLOOKUP
A few things are good to remember when you’re using VLOOKUP. First, as I mentioned previously, 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.
The second thing to keep in mind is that VLOOKUP will only ever return one value. If we’d used “Georgia” as the lookup value, it would have returned the score of the first student from Georgia, and given no indication that there are in fact two students from Georgia.
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 really big spreadsheets or you’re working with values that are organized by time. Here’s the syntax:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
[lookup_value], again, 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. And [range_lookup] is the same as above; leave it blank to get the nearest value when possible, or enter 0 to only look for exact matches.
For this example, I’ve created a new spreadsheet with generatedata.com. This one contains a row for each state, along with an SAT score (we’ll say it’s the average score for the state) in the years 2000–2014. We’ll use HLOOKUP to find the average score in Minnesota in 2013. Here’s how we’ll do it:
=HLOOKUP(2013, A1:P51, 24)
(Note that 2013 is not in quotes because it’s a number, and not a string; also, the 24 comes from Minnesota being in the 24th row.) As you can see in the image below, the score is returned:
Minnesotans averaged a score of 1014 in 2013.
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.
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:
=INDEX([array], [row_number], [column_number]) =MATCH([lookup_value], [lookup_array], [match_type])
In INDEX, [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’s [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 value 646 in column F, MATCH returns 4.
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 sixth cell of the City column, INDEX returns “Anchorage,” the value from row 6.
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 Waters, and you want to see what this student’s score was. Here’s the formula we’ll use:
=INDEX(F:F, MATCH("Waters", C:C, 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:
As you can see from the inset, Owen Waters scored 1720, 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!
Let the Search Begin
Microsoft Excel has a lot of extremely powerful functions, and the four listed above just scratch the surface. Even with this cursory overview, however, you should be able to save a lot of time when you’re working with large spreadsheets. If you want to see just how much more you can do with INDEX, check out “The Imposing Index” at Microsoft Excel Hero.
How do you run searches in big spreadsheets? How have you found these lookup functions to be helpful? Share your thoughts and experiences below!
Image Credits: magnifying glass on a spreadsheet Via Shutterstock