Excel's VLOOKUP function is excellent when you want to find a value in a table based on a lookup value. But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. This lesson shows you how to use the INDEX function (plus some other functions) to find all matching values in a list, and return a value from another column in the same row. It also looks at how to do this when you want to return all values which are a partial match (i.e. a wildcard search) to the values in your lookup table.

Here's a simple example based on a question I was asked by one of our readers.

• There are two worksheets, one containing Form data for a mail merge, and one containing Parcel data, some of which is to be included in the mail merge.
• The Form worksheet includes a row for each landowner who is to receive a letter. One of the columns in the spreadsheet is an ID for each landowner.
• The Parcel worksheet contains data about land parcels owned by the landowners in the Form worksheet.
• The mail merge will generate a letter to all land owners. That letter should include details of all of the land parcels owned by that individual land owner.

​In this lesson I'm going to focus on how to include that land parcel information in the mail merge, but I'm not going to discuss how to set up the mail merge with Microsoft Word.

Use the Index function to return multiple values a list

Let's say our Parcel worksheet looks like this:

As you can see, John Smith (ID H240) appears three times. We want to include a list of the crops John grows in our mail merge letter. If you've used a VLOOKUP function before, you'll know that we can use it to find the first crop for John in the list, but not the second or third.

Find all the row numbers to match our lookup value

Let's start solving our problem by first finding the row number of each row in which H240 appears, by using the formula below. After that we'll then extend this formula to find the crop value in each row:

{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(1:1))}

Here is a breakdown of this formula:

• The { } around the formula indicates that this is an array formula.
  • Note that you don't type these braces as part of the formula. You have to press Ctrl+Shift+Enter key when entering the formula to tell Excel it is an array formula, after which the { } will automatically appear. If you don't, our formula will return a #VALUE error.
  • If you're using Excel for Mac, you'll need to press CMD+SHIFT+Enter instead.
• The SMALL function has the syntax SMALL(array,k).
  • It looks up a list and finds the k'th smallest value in the array.
  • If k = 1 it will find the smallest. If k=2 it will find the second smallest value, and so on.
  • The second instance of the ROW function is used to find k in our formula. In this example, ROW will return the row number of row 1, which is, of course, 1.
• The IF function looks to see if the value in A9 is in the list.
  • In our example, A9 is where we'll enter H240 as our lookup value.
  • Note that the IF function will only return a value if our number is in the list.
  • If not, it will return a null value, which will cause the SMALL function to generate an error. We'll look at how to hide that error later.
• The ROW function is used twice:
  • If the IF function finds our value in the list, the ROW function returns the row number in which it was found. Note that it returns the row number of the worksheet, not the row number inside our table. This will be important later.
  • As noted above, the ROW function is also used in our formula to return k.

The screenshot below shows this formula in action:

In the screenshot above, I've entered h240 into A9 (you'll note that our formula isn't case sensitive). I've then entered the formula above into A11 as an array formula (don't forget that part!). It has returned 2, which is the row number for the first instance of H240 in the list. After that, I've copied and pasted the formula into rows A12 to A13. The formula has automatically picked up the correct row numbers, and generated an error when it couldn't find a fourth value.

However, there has been some magic under the hood that you can't see here. When I copied and pasted the formula into the additional rows, the array formula changed. Here are the formulas in each cell:

{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(1:1))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(2:2))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(3:3))}
{=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(4:4))}

Note how the ROW parameters in each formula are different: 1:1, 2:2, 3:3, 4:4. This is telling the SMALL function to find the 1st, 2nd, 3rd and 4th values in the list that match our lookup value. As I said, you don't need to edit the formula yourself - it updates itself when you copy and paste it.

Use the INDEX function to find the Crop values

Now that we know the row numbers for each instance of H240 in our example, we can use the INDEX function to use the row number in order to obtain the Crop value. If you're not familiar with the INDEX function, you can read our lesson on INDEX here (it will open in a new tab).

Here's the adjusted formula:

{=INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))-1,3)}

Here's how this function breaks down:

• The INDEX function looks in our table ($A$2:$B$7)
• It then uses the SMALL function to find which row to look in and then subtracts 1 from what SMALL tells us.
  • Remember that the ROW function returns the row in the worksheet where our value was found.
  • The INDEX function considers the first row of our table as row 1. Because our table data starts in row 2, we need to subtract 1 from the ROW value to get the correct row inside the table.
  • If you're applying this solution to your own spreadsheet, you may need to adjust this value to take account of where your data table is located.
• The INDEX function then looks in column 3, which is where the crop data is found.

Here's how our spreadsheet looks now.

Hide errors generated by our formula

At this point, we've done what we set out to do, which was find all of the crops related to John Smith. However, if we are going to use this solution in a mail merge, we don't want the mail merge to print #NUM in our letters. Let's finish off this lesson by looking at how you can hide error values when they occur in our formula:

{=IF(ISERROR(INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))-1,3)),"",INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))-1,3))}

This formula looks terrifying, but it's actually very simple.

• The IF function tests to see if our formula generates an error. It does this by using the ISERROR function.
• If the ISERROR function is true (i.e. our formula generates an error) then the IF function returns "", which means the cell containing our formula will appear to be blank.
• If the ISRROR function is not true, then our formula works OK, and the IF function will use it to return the correct value.

There's not much to see when we use this version of the formula, but here is what our spreadsheet looks like now:

Finding all matching values using partial match

A couple of people have asked in the comments below whether there was a way to use a wildcard, or partial match, for the lookup value in A9. The answer is yes - and here's the formula to use - you can replace the array formula in A11:A14 in the example above:

=IF(ISERROR(SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7))),"",INDEX($A$1:$C$7,SMALL(IF(IF(ISERROR(SEARCH($A$9,$A$1:$A$7)),FALSE,TRUE),ROW($A$1:$A$7)),ROW($C$1:$C$7)),3))

This formula uses the SEARCH function to determine if the lookup value in row 9 can be found in the cells in A10. In practical terms, this formula allows you to type a partial match, e.g. H24, and the formula will return all instances where H24 is found in column A. Note that you don't need to use asterisks, *, to indicate to Excel that you are using a wildcard - you simply type the partial string you want to match.

Note that there is a twist in this final example. You can't copy and paste the formula from A11 into A12 - A14 as you could in earlier examples. You have to do the following:

• Get the array formula working in A11, and returning the correct result (Beans in my example).
• Select all the cells where you want this formula to be (including the cell with the first working example). In my example it is A11:A14. Make sure the active cell is the one containing the formula.
• Click in the formula bar to start editing the formula but don't change anything.
• Press the keys to enter an array formula (CTRL+Shift+ENTER on PC or Command+Shift+Enter on Mac).
• The selected cells will change to show you the correct results, and the selected cells will become an array.

Once you do this, the selected cells can only be treated together as an array. If you try to edit, say, A13, you'll get an alert telling you that you can't edit an array. If you want to change your formula, you need to select all of the cells in the array, click in the formula bar, and then press the keys to re-enter the formula as an array formula. If you simply want to break the array, select all of the cells, click in the formula bar and press Enter.

Summary

And with that, we're finished. If you have any questions about this solution, please let us know in the comments!

* This lesson was inspired by a post by Ashish Mathur for Excel 2003. You can read that post here. The inspiration for this lesson came from a question from one of our readers on the original lesson on how to use the INDEX function. The formula for using a wildcard came from this forum discussion. All links open in a new tab.