One of the most common uses of Excel is as a database program, and oftentimes you've got to search the sheet for a specific value.
The go-to function for that is usually VLOOKUP, and understandably so. This function will take a table, find a value in the left-most column, and return the value from the desired column.
You type in =VLOOKUP, then you type in the value you want to search for, in quotes if it's text.
You select a table where the table's left-most column contains the value you're searching for.
You then tell VLOOKUP what column from the table that contains the answer, then tell it if you want an exact match.
Then, boom, the function finds "Eric" in the 9th row of column A, then returns the value in the fourth column, his age. Easy peasy.
However, there are a couple of disadvantages. Namely, you can only VLOOKUP a value from left to right. Also, processing times can get high for large databases.
To beat that, we're going to use INDEX/MATCH. INDEX/MATCH isn't a function, though it's two functions used in concert with one another.
First let's look at INDEX. With INDEX, you select an array, and then tell it a number, and INDEX returns that element.
So here, INDEX gave us the 2nd element of the B2:B9 array, which is New York.
Now let's look at MATCH. Match takes a value to search for in an array and returns which element it is.
We select the array C2:C9.
Then we enter 0, which means that we want an exact match.
So this tells us that "Math" is the second element of the array C2:C9.
If you haven't noticed, both of these functions are kind of useless. But used together, they're more powerful than any other search function.
We want to know what state the Math major is from. First, enter =INDEX, and select the array of home states.
Instead of entering an element number write in MATCH.
Then, fill in the MATCH function like we did earlier, searching "Math".
Select the column that contains the majors.
And of course type in 0 to get an exact match. Then, close the whole function. See what we did?
MATCH searched for "Math" in C2:C9, then passed the result (2) to INDEX, which returned the second element of the array B2:B9, which was New York. So the math major is from New York.
We can do the exact same thing with any of these columns. That's why it's more powerful than VLOOKUP because it can search from Right to Left.
Even more, with large data sets keeping the number of searchable cells down cuts processing time.
Of course it can also still go from left to right too, but it's much more robust than VLOOKUP.
What's more, you can't search for an exact math in an unordered list in VLOOKUP. Order doesn't matter here.
So next time you're searching through a vast database, don't use VLOOKUP.
Give the more robust, more exact and twice as useful INDEX/MATCH a try.
Want to start at the very, very beginning?
