How to Utilize Reference Functions in MS Excel 2013 (and Higher)

Chaitanya Tapase

Gathering data in Microsoft Excel isn’t all that hard. But, sorting, validating, adjusting, looking up values and such is pretty hard. Some functions may not be as popular as others, but if you know these, it will reduce your workload by a whole lot.

Complex Data Report
Don’t be afraid of finding the right data in complex sheets | Shutterstock

Let’s look at Reference Functions in MS Excel.

What are They?

When you want to find a match (not the Tinder kind), you might need to scan a long while in an Excel sheet overflowing with data. But, there are functions that will do the hard work for you, provided you use them the right way.

All you need to do is supply the value you’re searching for, a range to search within and an (optional) argument on how to perform the search. Usually, this argument is specified by the number 0, meaning you want an exact match.

How Does it Work?

So, let’s say you were searching in a big Excel file for a certain value and the function found it in the 3rd row, the Match function will return a value of 3. Match functions work in conjunction with the Index functions, sometimes Match providing the index for the Index function.

Matching ain't easy
Matching ain’t easy

Ok, don’t let that confuse you. Let’s look at a straightforward example where an excel sheet of Orders against Profit has been used. Here we’re looking for the maximum profit but also looking at the order quantity corresponding to it. Since it’s a simple sheet, we can pinpoint them easily, but what if it was 10 times more complex?

Simple Data

That’s when we need to know formulas. And do that in the Profit row, I need to simply enter the below formula –

=Max(B4:B8)

This formula needs to be entered where you wish to see the results. Now, to get the corresponding Order quantity matching the result you get from the above formula, you need to use the Index function. So, below the Order Quantity table, type this formula –

=Index(A4:A8,match(B10,B4:B8,0))

Again, the 0 used in the end only indicates that we want an exact match to be displayed.

Handle With Care

If your Excel data is more complex you need to be more careful. I’ll show another example, where I’ve taken the same numbers as the previous example but also added additional columns for stockout costs. How do you find the best profit and corresponding order quantity from here?

It’s simple, we kill the Batman. Um, I mean you kill the overthinking of your brain functions and simply use the same formulas for all the cells where results need to be shown. And the best way to drag a formula to adjacent cells is to drag it across.

Excel Functions

This can be done for both, Match as well as Index functions.

Excel Functions-1

Please Note: We add the $ symbol in the formula to keep the ‘reference’ column constant, as the A column is constant for all inputs here.

Ex-Cell?

We’d like to hear how many of you really find more usage of MS Excel at your work or school. Also, what are the common hurdles you face while using it. Reach out to us via our discussion forum.

Also See
#msexcel#office 2013

Join the newsletter

Chaitanya Tapase has varied interests in Technology from Android to Windows to DSLR Photography. On weekends, you'd find him catching up on TV Shows like Game of Thrones and enjoying House Music.