Perform Complex Lookups with INDEX, MATCH and Data Validation Lists

Locked lesson.

Upgrade

Quick reference

Perform Complex Lookups with INDEX, MATCH, and Data Validation Lists

Learn how to combine the INDEX and MATCH functions to create a powerful and flexible lookup formula. 

When to use

We use INDEX and MATCH to perform a lookup whenever we are looking up information in a table that exists in a column to the left of the lookup value. 

Instructions

VLOOKUP is by far the most popular and best-known out of all of the lookup functions in Excel. However, it has its limitations. 

The Limitations of VLOOKUP

The main limitation of VLOOKUP is that it can only search from left to right. This means that our lookup value must exist in a column to the left of the value we need to return. 

Take a look at the screenshot below. If we want to perform this lookup using VLOOKUP, we quickly run into problems. 

The lookup value is the 'Company' and we need to return the 'Company No'. However, the 'Company No' is to the left of the 'Company' column in the table and VLOOKUP can only search left to right. 

When we need to specify the column to return in the formula, we count the columns from the lookup value column. As the 'Company No' is to the left of the 'Company', the most logical thing to do would be to enter '-1' to tell VLOOKUP to look backwards (right to left). 

Unfortunately, negative values are not valid column index numbers so the formula doesn't work. 

This is where we can use INDEX and MATCH instead as these formulas combined can lookup any information regardless of where the lookup value is in the table. 

What is INDEX?

INDEX and MATCH are two separate functions in Excel that are often used together to perform powerful, flexible lookups. 

Let's look at what INDEX does on its own. 

The INDEX function allows us to index a range of cells and return a value from the row number we specify. 

The first argument, array is always the range of cells that contains what we want to return. We want to return the 'Company No' so we need to select the 'Company No' array. 

In this example, we are looking up the company 'Gaggle'.  If we look in the table, 'Gaggle' is in row number 6. 

This produces the correct result BUT we had to manually supply the row number. That's easy in a small dataset like this, but not so easy if we have a dataset with thousands of rows. 

With that in mind, let's take a look at the MATCH function on its own. 

What is MATCH?

The MATCH function will look for a value we specify in a range of cells and return the row number. 

In this example, we are matching 'Gaggle' (J7), in the company range (C8:C15) and we are doing an exact match of the word 'Gaggle' in the table (0). 

The MATCH function returns the row number. 

Use INDEX and MATCH Together

We've established that the INDEX function tells Excel what column of information we want to return and the MATCH function automates the finding of the row number.

We can combine INDEX and MATCH to perform a powerful lookup. 

We INDEX the array B8:B15 (where our answer is), then we go straight into MATCH to find the row number. 

We can use the same formula to return the 'Profit' we just need to modify our array selection to index the 'Profit' column. 

Data Validation and Lookups

We can add data validation to make it simple to switch between companies. 

  • Click in the cell where the data validation needs to go. 
  • From the Data tab, in the Data Tools group, click Data Validation. 
  • From the Settings tab, click the drop-down arrow underneath Allow and choose List. 
  • In the Source field, select the list of companies. 
  • Click OK. 

We can now use this drop-down to quickly cycle between different companies. 

The INDEX and MATCH formula always uses the value in cell C7 as the lookup value so when it changes, the formulas will update automatically. 

Login to download
  • 00:04 In the previous lesson, we saw how we can use VLOOKUP to look up
  • 00:08 information in a table based on a lookup value.
  • 00:12 And VLOOKUP is far and away one of the most popular functions in Excel.
  • 00:17 But it does have its disadvantages, so in this lesson we're going to take
  • 00:22 a look at why VLOOKUP isn't appropriate in all situations.
  • 00:26 And then I'm going to show you a much more flexible alternative
  • 00:31 using two functions, INDEX and MATCH.
  • 00:34 So let's dive straight in.
  • 00:36 Now here I have a small table of data that shows company numbers, company names,
  • 00:42 the revenue, the cost, the profit, and also the percentage profit.
  • 00:47 And this is a small table of data that I just have on the same worksheet
  • 00:51 as where my outputs are going to go.
  • 00:53 But it might be that you have this data on a different worksheet,
  • 00:57 it doesn't really matter because the formula works in the same way.
  • 01:00 So I've just put all of this together for ease of demonstration.
  • 01:04 Now what I'm aiming to do here is I want to be able to
  • 01:07 select a company from a data validation dropdown list, and
  • 01:12 have it return from the table the company number and the profit.
  • 01:16 When I select a new company from the dropdown it's going to update and
  • 01:20 return the correct company number and profit.
  • 01:23 So in this first example I'm not going to create a data validation dropdown list,
  • 01:28 I'm simply just going to type In a company name from my table.
  • 01:31 So let's choose Gramazon.
  • 01:34 Now the first piece of information that I want to return for Gramazon is the company
  • 01:39 number, so I might think to myself I can use VLOOKUP to do this.
  • 01:43 So let's try and use VLOOKUP, because you'll soon find that we have some issues.
  • 01:48 Now the first argument is our lookup value, so
  • 01:50 what are we looking up in the table?
  • 01:52 Well we're looking up Gramazon, the company name, so let's select cell J7.
  • 01:57 Where are we going to find it, what's the table array?
  • 02:00 Remember, your table array selection must include not only the lookup value, but
  • 02:05 also what you want to return.
  • 02:07 If in doubt, select everything, that's my motto.
  • 02:10 So let's select the entire table, B8 to G15.
  • 02:14 The next thing is the column index number.
  • 02:17 Now if you recall when we were looking at VLOOKUP in the last lesson,
  • 02:21 I told you that when you're trying to work out what the column index number is
  • 02:26 VLOOKUP numbers columns in a table from left to right.
  • 02:29 Now what are we returning here?
  • 02:31 Well we're using Gramazon the company as the lookup value, but
  • 02:35 we want to return the company number which is to the left of our lookup value.
  • 02:40 So how do I specify that as a value?
  • 02:43 If I say 1, well it's not column 1 because we count from wherever we
  • 02:48 have the lookup value column.
  • 02:50 Now in most datasets, your lookup value column will be in that first column,
  • 02:55 so on the left of your dataset.
  • 02:57 But in situations such as this where the lookup value
  • 03:01 column is to the right of what we want to return, VLOOKUP doesn't work.
  • 03:06 So what you might think you can do here is okay, well it's minus one,
  • 03:10 it's back one from that company column.
  • 03:12 So I'm going to type in -1, let's see what happens.
  • 03:15 Let's put FALSE on the end to do an exact match of that company name.
  • 03:19 When I hit Enter I'm getting an N/A error and that is because of that
  • 03:25 column index number, -1 isn't a valid input for the formula.
  • 03:30 So because VLOOKUP can only count from the lookup column to the right,
  • 03:36 one, two, three, four, it can't count backwards to grab that company name.
  • 03:42 So this is where we need to think about an alternative lookup function.
  • 03:46 Now for those of you with the newest versions of Excel you've probably familiar
  • 03:51 with XLOOKUP, and we are going to talk more about that in the next lesson.
  • 03:55 But in this lesson I want to show you the formula that you can use even if you
  • 03:59 don't have the latest version of Excel.
  • 04:02 And it's essentially two formulas in one, INDEX and MATCH, so let's take a look
  • 04:07 at what they do individually so you can understand why they work so well together.
  • 04:12 Now before we construct our formula, let's just put our company names into
  • 04:16 a dropdown list so we don't have to keep typing them in.
  • 04:19 So let's jump up to the Data tab, click the dropdown next to Data Validation.
  • 04:25 We're going to create a list, and
  • 04:28 my source is going to be my list of companies.
  • 04:32 Let's click on OK, and now I have my helpful dropdown list.
  • 04:36 Now if I want to return the company number, let's see what INDEX does.
  • 04:42 Now INDEX, the first argument here is array.
  • 04:45 Now the way that I remember what to select here is the array
  • 04:50 is always what you want to return.
  • 04:53 So I want to return the company number, so
  • 04:56 my array is going to be the company number array over here.
  • 05:00 The next argument is the row number.
  • 05:03 So if I take a look at the company I'm looking up the company is Lemon,
  • 05:08 if I look at my table I can see that Lemon is the second value in this list,
  • 05:14 so my row number is 2.
  • 05:16 Let's close the bracket and hit Enter, and
  • 05:19 it returns the company number LEM-4526 which is correct.
  • 05:24 But there was a certain element of having to manually count down to get that row
  • 05:29 number argument, and that is fairly simple in this very, very small dataset.
  • 05:34 But imagine if you had a dataset that had 100,000 rows,
  • 05:38 are you really going to sit there counting down to find out
  • 05:42 the row number of the value that you want to return?
  • 05:45 No, you're not, so with that in mind let's take a look at what MATCH does on its own.
  • 05:52 So let's type in =MATCH, we have three arguments here.
  • 05:58 The first argument is the lookup value, so
  • 06:00 again that is whatever we've selected in that data validation dropdown.
  • 06:05 Where does this value Lemon exist, well it exists in the Company range.
  • 06:11 Match type, do I want to exactly match the word Lemon in that column?
  • 06:16 Yes, I do, I want an exact match, so we need a 0.
  • 06:21 Close the bracket, hit Enter, and check out what the MATCH function returns.
  • 06:25 It returns the row number,
  • 06:27 it's telling me that Lemon is the second value in this table.
  • 06:31 So you can probably get an idea as to what we can do here.
  • 06:34 We can combine the INDEX with the MATCH to get exactly what we're looking for
  • 06:39 because the MATCH function essentially automates the finding of that row number.
  • 06:45 So let's take it from the top.
  • 06:47 =INDEX the array, remember that's where our answer is contained, comma.
  • 06:54 Now when we get to row number, this is where we want to automate the finding of
  • 06:59 it by going straight into our MATCH.
  • 07:04 Our lookup value is whatever we've selected in the data validation dropdown.
  • 07:09 We're looking for that in the Company name column, and we're doing an exact match.
  • 07:15 We then need to close off our MATCH function,
  • 07:19 close off our INDEX, hit Enter, and we get the correct result.
  • 07:24 What about if we change our selection in our data validation dropdown to Microhard?
  • 07:29 Let's take a look, they're all looking good so far.
  • 07:33 Let's take a look at Fazebook, yes, everything is working correctly.
  • 07:37 So INDEX and MATCH are a much more flexible way of performing lookups because
  • 07:42 it doesn't matter where the lookup value is in relation to what you want to return.
  • 07:48 Let's do it one last time to grab the profit, just so
  • 07:51 you can get more of an idea as to how this works.
  • 07:54 So INDEX, what are we indexing this time?
  • 07:57 Well we're looking for the profit, so we're indexing the Profit column.
  • 08:03 We then go into our MATCH to find that row number.
  • 08:05 Our lookup value this time is Fazebook.
  • 08:08 Where are we looking it up, in the Company name column, and
  • 08:12 we're doing an exact match, we're getting a result of 30,000.
  • 08:16 And if we check this out, yes, the profit here is 30,000.
  • 08:20 If I change my selection to Samsing let's check to make sure that's all correct,
  • 08:27 yes, it is.
  • 08:28 So that is how you can use INDEX and
  • 08:30 MATCH along with data validation to perform really powerful, complex lookups.

Lesson notes are only available for subscribers.

Gift this course