The One Microsoft Excel Trick That Rules Them All

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 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.

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.

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.

Instead of entering an element number write in MATCH.

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?

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 maths 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.

Business Insider Emails & Alerts

Site highlights each day to your inbox.

Follow Business Insider Australia on Facebook, Twitter, LinkedIn, and Instagram.