Two-Way Lookups with INDEX and MATCH or XLOOKUP

Locked lesson.

Upgrade

Quick reference

Two-Way Lookups with INDEX and MATCH or XLOOKUP

Use INDEX and MATCH or the new XLOOKUP function to perform a two-way lookup in a table. 

When to use

We do a two-way lookup whenever we have two pieces of criteria to look up in a table. 

Instructions

A two-way lookup differs from a standard 1-way lookup in that we are using two lookup values to find our answer in the table. This situation occurs when we have column headings and row headings in a table and need to use both to find the answer. 

In this example, we need to look up the month and the team in order to find our answer. 

We can complete this task using INDEX and MATCH or the new XLOOKUP function. 

Use INDEX and MATCH to Perform a 2-Way Lookup

  • Type =INDEX into the cell. 

Note that the INDEX function has two arguments, row_num, and column_num (optional). In the last lesson, we only used the row_num argument as we only had one lookup value. This time we need to use both. 

The array is the range of cells where our answer is. Our answer could be any of the values in the table. 

  • For the row_num argument, we are going to use MATCH to find the row number for us. 

We are matching the first piece of criteria, 'Sales Team 1' (B16) in the 'Team' range (B8:B12) and we are doing an exact match (0). 

 We can then move on to the column_num argument where we specify our second piece of criteria with another MATCH. This time, we are matching the 'Month' (C15) in the 'Month' range (C7:N7) and we are doing an exact match.  

Don't forget to close off the parenthesis for both MATCH and INDEX. 

The formula returns the correct result from the table. 

Use XLOOKUP to Perform a 2-Way Lookup

XLOOKUP is one of the newer functions in Excel and is only available in Excel for Microsoft 365, Excel 2019, and Excel 2021. XLOOKUP is as powerful as INDEX and MATCH but isn't as complex to understand. 

Let's perform the same lookup but use XLOOKUP instead. 

XLOOKUP has 6 arguments, the last 3 are all optional. XLOOKUP requires us to provide the lookup_value (what we are looking up), the lookup_array (where we will find the lookup value), and the return_array (what we want to return). 

The first lookup value is 'Sales Team 1' (B16) and we are looking it up in the 'Team' range (B8:B12). 

 

Instead of typing the return_array we need to add our second lookup. 

The lookup value is 'Aug' (C15) and we are looking it up in the 'Month' range (C7:N7). 

We can now provide the return_array (where our answer is). 

The correct result is returned from the table. 

 

Login to download
  • 00:04 In the previous lesson, we saw how we can use INDEX and
  • 00:07 MATCH to perform a more complex lookup.
  • 00:10 And in this lesson I want to show you how you can Use INDEX and
  • 00:14 MATCH to perform a two-way lookup.
  • 00:16 And we're also going to take a quick look at XLOOKUP as well.
  • 00:19 And first of all what exactly do I mean by a two-way lookup, what is that?
  • 00:23 Well it's basically where we're looking up two pieces of information as
  • 00:28 opposed to one.
  • 00:29 In the last example, we were just using one piece of criteria.
  • 00:32 We were selecting a company name, and
  • 00:35 returning information based on the company name only, one piece of criteria.
  • 00:41 In this example, we need to use two pieces of criteria.
  • 00:46 Because what we want to do here is we want to return the sales, but
  • 00:51 we want to specify the sales team and also the month that we're interested in.
  • 00:57 So we have two pieces of information to look up, the team and the month.
  • 01:02 And you can see underneath this table I've created data validation dropdown lists.
  • 01:07 Now we've done this already a few times in this section, so
  • 01:10 I'm going to leave that to you to set up yourself.
  • 01:13 But all I have basically in here are all of the different sales teams,
  • 01:16 there are five of them.
  • 01:18 And then in this dropdown, we just have the months of the year.
  • 01:21 And I've built both of these based off of the values that we have in the table
  • 01:25 up here.
  • 01:26 So I need my lookup to be able to look for Sales Team 1, and
  • 01:30 then also look for August in the table to return the correct result,
  • 01:35 and this is what we classify as a two-way lookup.
  • 01:38 Now in this first example we're going to use INDEX and MATCH to do this, so
  • 01:43 this gives us another good opportunity to practice utilizing this but
  • 01:47 also expand our knowledge out.
  • 01:49 So let's go for INDEX first of all.
  • 01:53 Now remember the array is what you want to return.
  • 01:57 Now my answer could be anywhere in this table,
  • 02:00 so I need to select all of the numbers because it could be any of them
  • 02:05 depending on what I've selected in these dropdowns.
  • 02:09 Now before we continue, just take a look at the different arguments.
  • 02:11 We have array, row number, and then we have column number in brackets, so
  • 02:16 remember column number is the optional argument.
  • 02:19 Now in the last lesson we didn't need to provide a column number,
  • 02:23 it was just the row number that we gave it.
  • 02:25 But this time we're going to be using both the row number and
  • 02:28 the column number to get our result.
  • 02:31 So the first thing to deal with here is the row number, and
  • 02:34 we're going to use MATCH to automate the finding of that row number.
  • 02:37 Now our lookup value, well we have two of them so let's start with the first one.
  • 02:41 Let's start with Sales Team 1, that's our first lookup.
  • 02:45 Where are we looking up Sales Team 1?
  • 02:48 Well we're looking it up in the Sales Team range, just here.
  • 02:52 Are we doing an exact match,
  • 02:54 do we exactly want to match the words Sales Team 1 in that range?
  • 02:57 Well yes we do, so we want a 0 on the end.
  • 03:01 If we close off our MATCH and press comma,
  • 03:04 it then moves us on to the column number argument.
  • 03:08 So this is where we can go in with our second piece of
  • 03:11 criteria because we need to find which month, which column we want to return.
  • 03:15 So we need to do another MATCH, this time we're looking up the month.
  • 03:20 Where are we going to find that month, what's the lookup array,
  • 03:24 well we're going to find it up here.
  • 03:26 Are we doing an exact match of the word August in this case, or
  • 03:31 AUG, yes, we are so we're going to put a 0.
  • 03:34 Close off our MATCH, close off our INDEX, hit Enter, and we get our result.
  • 03:40 Now I'm just going to apply some comma style formatting and
  • 03:43 take those decimal places down so that it looks the same as the values in the table.
  • 03:47 Now let's check that this is correct.
  • 03:49 So Sales Team 1, let's take a look at August, 3,533, perfect.
  • 03:55 What about if I change the month to June?
  • 03:58 Let's take a look, Sales Team 1, June, 6,005.
  • 04:03 Let's change the sales team this time to Sales Team 5, June, 9,017.
  • 04:11 So my two-way lookup is working perfectly.
  • 04:14 So now that we've done this using INDEX and MATCH,
  • 04:17 let's take a look at how we would do this using XLOOKUP.
  • 04:21 Now XLOOKUP, as I mentioned, is only available in the later versions of Excel.
  • 04:26 So if you have Excel for Microsoft 365, Excel 2019, or
  • 04:31 Excel 2021, then you're going to have XLOOKUP.
  • 04:35 And XLOOKUP is really a simpler way of doing an INDEX and MATCH,
  • 04:39 at least I think it's simpler.
  • 04:41 So if we type in =XLOOKUP we have a lot more arguments, but
  • 04:46 only the first three are mandatory, the rest are optional.
  • 04:50 Now the reason why I find this simpler is because I find the terminology, or
  • 04:54 the language, used in these arguments a little bit easier to understand than in
  • 04:58 the INDEX and MATCH functions.
  • 05:00 So the first argument is the lookup value.
  • 05:03 So we're looking up Sales Team 5 lookup array,
  • 05:07 where are we going to find it, this range just here, comma, return array.
  • 05:12 Well this is where we need to go straight into our next
  • 05:16 XLOOKUP to define our other lookup value.
  • 05:20 So we want to select June this time, we're looking that up in
  • 05:25 this range just here, and now I can specify the return array.
  • 05:30 So the return array, which is where our answer is going to be found,
  • 05:34 is everything that we have in here.
  • 05:36 Close off the first XLOOKUP, close off the second, hit Enter, and we get our result.
  • 05:41 Let's double check to make sure it's correct, 9,017.
  • 05:46 If I change this to, let's say, October and Sales Team 2,
  • 05:51 let's go across and check, 3,014.
  • 05:54 So that's two different methods that you can use to perform a two-way lookup,
  • 06:01 using INDEX and MATCH, and also using the new XLOOKUP function.

Lesson notes are only available for subscribers.

Gift this course