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. 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? That’s when we need to know formulas. And do that in the Profit row, I need to simply enter the below formula – 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 – 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. This can be done for both, Match as well as Index functions. 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. The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.
