Locked lesson.

Upgrade

About this lesson

In this lesson, we learn the basic uses of INDEX and MATCH by building your own invoice template.

Exercise files

Download this lesson’s exercise file.

Invoice template IM.xlsx
23.1 KB

Quick reference

INDEX & MATCH Functions

Learn the basic uses of INDEX and MATCH

When to use

These functions are useful to use a value in one dataset to find a similar value in another set and return a related value and are especially useful when VLOOKUP is limited.

Instructions

  • INDEX() functions as a map with coordinates. The three arguments would be the map (array) and the row and column coordinates and INDEX returns the value of the cell at that point on the map (array)
  • INDEX(array, row, [column])
  • MATCH() returns the position of a value in a range.
  • MATCH(lookup value, lookup array, [match])
  • Combining the two will replace the VLOOKUP with fewer limitations
  • The MATCH function will look for the value and return the row or column number where it finds that value. This value is then used to replace either the row or the column reference in the INDEX function.
  • MATCH mode dictates exact or approximate match
  • Note the use of $$ to make copying the formula more effective
Login to download
  • 00:04 In this example, we have an invoice.
  • 00:07 If we choose a client code by validation list, and an item code by validation list,
  • 00:13 and fill in a quantity, we want Excel to complete the entire invoice for us.
  • 00:20 The client information it gets from the list on the right.
  • 00:23 The item code details, we also get from another list below that one.
  • 00:27 Now to get the item details from the list onto the invoice,
  • 00:31 we previously used a lookup function.
  • 00:34 This time, we're going to use an index and a match function.
  • 00:39 The match is the actual lookup function, index is a reference function.
  • 00:44 The combination of the two will be similar to using a vlookup,
  • 00:49 hlookup, or an xlookup.
  • 00:51 Before we start, the index function returns a value in a specific spot.
  • 00:56 For example, I'll tell index that this is my map.
  • 01:00 Give me what's in coordinate (2, 2).
  • 01:04 2, the row value, one, two, and 2 for the column value, one, two.
  • 01:12 So coordinate (2, 2) would return jamming.
  • 01:16 Then match, if I have a list and I would tell it to look for
  • 01:20 556786, match will look in this column for that number.
  • 01:24 And it will return the position, which is one,
  • 01:29 two, three, four, five, the position is 5.
  • 01:34 Match can look for a position in a row also.
  • 01:38 423 will be in position one, two, three.
  • 01:42 So let's put them together.
  • 01:44 Let's start with index.
  • 01:46 I know I'm looking for item code 556786.
  • 01:50 So I give index my array, and I tell it that the item name that
  • 01:56 I want in the description field is in a specific position.
  • 02:01 We put in the row number first, one, two, three, four, five.
  • 02:07 And then column number, one, two.
  • 02:10 Row 5, column 2, close the parentheses,
  • 02:15 and hit Enter, and I have flute.
  • 02:18 The problem with that is, if I change my item code position,
  • 02:22 (5, 2) is no longer correct.
  • 02:25 If we make any changes or additions to this inventory list,
  • 02:29 the row number will change.
  • 02:30 So let's replace that 5 with a match function.
  • 02:34 match, I'm going to say the item code is my lookup value.
  • 02:39 This list over here is my array where Excel's going to look for
  • 02:44 that, and I want an exact match.
  • 02:48 The arguments are similar to other lookup functions.
  • 02:51 So what this match function is saying,
  • 02:55 find the red inside the purple exactly, and return the position.
  • 03:00 It's going to give me a position of one, two, three, four, five.
  • 03:05 So that match is going to return a 5.
  • 03:07 And then index is going to look in the blue for row number 5, column number 2.
  • 03:13 Column number 2 does not have to be relative, it can be hardcoded because I'm
  • 03:17 going to use column number 2 for all the descriptions.
  • 03:20 So when we hit Enter, we get flute.
  • 03:25 Now let's do the same thing for price.
  • 03:28 index, there's my array, the entire range,
  • 03:33 item code is 5, but we need to make that relative.
  • 03:39 So we can't hardcode that number in.
  • 03:42 We can use the match function again.
  • 03:44 There's my lookup value and there's my lookup array, and I want an exact match.
  • 03:52 So match is going to give me row 5.
  • 03:55 You can find the price in column one, two, three, so comma and
  • 04:01 3, close parentheses, hit Enter, and there's our price.
  • 04:07 And if I update my item code, everything updates with it, so that's perfect.
  • 04:14 Now let's look at the client field, that's item 423.
  • 04:17 Now, once I start typing, the 423 goes away, but let's remember that.
  • 04:22 This is my array, excluding headings.
  • 04:25 In which row will I find my client name, row 2, and that stays fixed.
  • 04:31 But which column am I looking for?
  • 04:35 423 is going to be in column 3.
  • 04:40 So let's do a match.
  • 04:42 Find the value in (8, 7) in this array, it's an exact match.
  • 04:49 So that replaces my column number in the index function.
  • 04:54 And look at the formula again, I want the value that's in the blue array,
  • 04:59 row two, but which one of the three?
  • 05:02 The one where 423 or whatever the value is that I have in (8, 7).
  • 05:07 That's one, two, three in this instance, so
  • 05:12 it's row 2, column 3, it will return notes.
  • 05:17 And that's index and match.
  • 05:20 To copy the formula down, I'll need to lock all my values to stay fixed.
  • 05:27 Now I have change the row number from 2 to 3 if I copy it down.
  • 05:32 This is the same problem that we had with the hlookup.
  • 05:38 And that's how the index and match function works,
  • 05:42 solves some of the limitations of the vlookup and hlookup.

Lesson notes are only available for subscribers.

Gift this course