We’ve been writing a lot about Microsoft Excel formulas.
The program is the gold standard of programs. It’s elegant, ubiquitous, and outstandingly powerful.
American business lives and dies by the spreadsheet, and everyone is always looking to hone their skills.
There’s one trick, though, that separates the quants from the interns.
That trick is Index/Match, a function that can find any value in any spreadsheet.
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 then tell VLOOKUP what column from the table that contains the answer, then tell it if you want an exact match.
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.
Now let's look at MATCH. Match takes a value to search for in an array and returns which element it is.
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 maths major is from. First, enter =INDEX, and select the array of home states.
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.
What's more, you can't search for an exact maths in an unordered list in VLOOKUP. Order doesn't matter here.
Business Insider Emails & Alerts
Site highlights each day to your inbox.