VLOOKUP (Pronounced V Lookup, not vloo-kup, in case you were wondering) is great. I love it, and use it all the time, but it does have complications. Try to explain it to anyone who’s not comfortable writing formulae, and it can be troublesome.
Essentially, any lookup just needs to find a specific value within a range, and return the corresponding value in another range. Vlookup does this admirably by finding the value and counting across a specified number of columns, but strictly speaking, this isn’t necessary.
INDEX provide ‘lookup’ and ‘return’ functions to do this without needing to declare the whole range or a number of rows or columns, and you can use them independently of each other or nested together. Today, I’m going to use them individually to show you what each function is doing, before demonstrating how to write them together.
MATCH function is our lookup. It’s our way of saying find this item in a range, and tell me where it occurs. The syntax for
=MATCH(Thing_I'm_Matching, Range_I'm_Looking_For_It_In, Optional_Match_Type).
Let’s take a set of numbers in a range. You’ll see in C3:E15, there are a swathe of numbers. In Column B, I’ve annotated the position of each item in the column for clarity.
Let’s say I want to find the number 166 in the range in column C. Simply put, just type
As you can see, this returns a 6. If we take a look at the formula editor by clicking the fx icon, you can see how this works. We’ve asked to find the number 166 in a range which contains 1, 34, 67, 100, 133, 166, and so on.
You may also notice that I didn’t use the optional match type. This allows you to specify whether you only want to return the position that shows an exact match, by entering the match type 0, the nearest match below the lookup value (1), or the nearest match above the lookup value (-1). For the match type 1, your range will need to be sorted in ascending order, and conversely for -1, your range needs to be sorted in descending order.
If you don’t specify a match type, match type 1 will be used. So if you need an exact match, specify the type as 0 to be on the safe side!
INDEX function is the opposite – it returns the item in a range matching a specified position. The syntax is:
To find the 6th, 5th, and 8th item in the ranges we used above, it’s just a case of declaring a range and specifying the positions in those ranges:
This works for any single row or single column range. But what if we want to index something in a larger range that spans both rows and columns? Thankfully that’s easy too. The syntax just needs a slight change:
=INDEX(Range_I'm_Looking_In, Row_To_Return, Column_To_Return)
As you can see, I’ve updated my formulae in the screenshot below, and they resolve correctly just as before, just by counting down the rows before counting across the columns:
Clever, huh? Well now comes the good bit. I’ve got a data set that, while full of guff, I need to look across. I’d like to find the value in the last column which corresponds to the value “166” in the first column:
Well, if we can find the position of “166” in the first column, we can simply then find the value in the equivalent position in the final column. We can use
MATCH to show that 166 is in 6th place, and then
INDEX to show what is in 6th position in column J:
Equally, we can join this together, by nesting the
MATCH inside the
INDEX – asking excel in a single formula to return the value that can be found in position “X”, where “X” is the position that our lookup vale is in another column:
INDEX(MATCH) is really versatile. Building this nest up together, we can easily create a formula which can return a lookup from multiple columns and rows at one, all based on dynamic values:
Here, I’m looking up in the data body (C58:F64, reaching from the top left 34 down to the bottom right 29.), so that’s the range in my
INDEX function. I’m using a range with multiple rows and columns, so I need to declare the column number first. Easy peasy, just
MATCH the position in B58:B64 (the weekdays) where you find the value that’s in H59, “Monday”. This will match will return the value 1, since Monday is the first item in the B58:B64 range. Next, I need to find the column number, so again, I just
MATCH the position in C57:F57 (the names), where we can find the value in I59, “Jane”. This resolves to 4.
Accordingly, Excel will use the range C58:F64, and from the top left, will take the first row, fourth column, in this case the value in F58, “28”. While this seems like an overly complicated way to achieve this, now I can simply type in another day or name, and the value will update:
VLOOKUP, or even
HLOOKUP, we would have had to change ranges or lookup columns, whereas
INDEX(MATCH) gives us a scalable and dynamic solution.
Thanks for reading – feel free to ask questions in the comments field below, and be sure to share this post if you found it useful! Follow us on twitter or like us on Facebook to keep receiving our regular updates. Take care, and keep Excel-ling!