In this **Excel VLookup to return multiple values (with the INDEX function) Tutorial**, you learn how to:

- Carry out a VLookup and return multiple values;
- With an array formula using the following functions:
- INDEX.
- SMALL.
- IF.
- ROW.

This Excel VLookup to return multiple values (with the INDEX function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

Use the following Table of Contents to navigate to the Section you're interested in.

## Related Excel VLookup Tutorials

This Excel VLookup to return multiple values (with the INDEX function) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to open.

## The VLookup Return Multiple Values (with the INDEX Function) Formula Template/Structure

The following is the VLookup return multiple values (with the INDEX function) formula template/structure I explain (step-by-step) in the Sections below.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
=INDEX(ColumnWithValueToReturn,SMALL(IF(LookupValue=ColumnYouSearchIn,ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1),ROW(SequentialNumber:SequentialNumber)))
```

This is an array formula. If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
{=INDEX(ColumnWithValueToReturn,SMALL(IF(LookupValue=ColumnYouSearchIn,ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1),ROW(SequentialNumber:SequentialNumber)))}
```

## The Example Before VLookup Return Multiple Values (with the INDEX Function)

This Excel VLookup to return multiple values (with the INDEX function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. **You can get this example workbook (for free) by clicking the button below**.

The example worksheet has 3 tables/sections with the following characteristics.

**(1) Table 1 (Cells A7 to H27).**

The table:

- With the data.
- Where you search with the VLookup to return multiple values (with the INDEX function) formula.

Main characteristics:

- 8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
- 1 header row (row 7).
- 20 entries (rows 8 to 27).

**(2) Lookup Value (Cells J7 and K7).**

- A label (cell J7); and
- The criterion/value (lookup value) you look up in Table 1 (cell K7).

**(3) Table 2 (Cells J9 to K14).**

The table where you:

- Set up the VLookup return multiple values (with the INDEX function) formulas.
- Display the results.

This table has the following 2 columns:

- Total Sales: Cells J10 to J14:
- Are currently empty.
- Will store the VLookup return multiple values (with the INDEX function) formulas.

- Formula: Cells K10 to K14:
- Currently display the #N/A error.
- Will display the VLookup return multiple values (with the INDEX function) formulas.

## Step 1: Set Up the VLookup Test

Create an expression that does the following:

- Test whether the values stored in the column you search in (with the VLookup criteria) match the value you search for (the lookup value); and
- Return a Boolean value (TRUE or FALSE) for each row (in the column you search in), indicating whether the value stored in the applicable row matches (or not) the lookup value.

To do this inside a VLookup return multiple values (with the INDEX function) formula, work with the equal to comparison operator (=).

Use the following template/structure to set up the VLookup test:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
LookupValue=ColumnYouSearchIn
```

The expression template/structure I describe above returns an array of TRUEs and FALSEs.

### Step 1 Example

In the example worksheet:

- The lookup value is stored in cell K7.
- The column I search in is column A (cells A8 to A27; $A$8:$A$27).

The resulting expression (using absolute references) is:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
$K$7=$A$8:$A$27
```

The image below illustrates how this expression returns:

- TRUE or FALSE for each row (in the column I search in; column A), indicating whether the value stored in the applicable row matches (or not) the lookup value (Salesperson 10).
- Column I (Step 1 Array) returns TRUE or FALSE, depending on whether the value stored in column A (Salesperson) of the applicable row matches the lookup value (Salesperson 10).
- Cell J8 displays the formula (using the basic structure of the expression carrying out the VLookup test).

- An array of TRUEs and FALSEs.

## Step 2: Create an Array with the Numbers of the Rows You Work With

Work with the ROW function to create an array with the numbers of the rows you work with (the rows of the column you search in).

The ROW function returns the row number in the worksheet.

When creating a VLookup return multiple values (with the INDEX function) formula, the number of the first row you work with must be 1 (regardless of its actual location in the worksheet).

Therefore, you must adjust the value returned by the ROW function to ensure that:

- The number of the first row you work with is 1.
- The number of the second row you work with is 2.
- …
- The number of the last row you work with (assuming you work with n rows) is n.

To achieve this, do the following:

- Obtain the number of each row you work with. To achieve this, specify the Reference argument of the ROW function as an absolute reference to the column you search in.
- Subtract the number of the first row you work with. To achieve this, specify the Reference argument of the ROW function as an absolute reference to the first cell in the column you search in.
- Add 1.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1
```

### Step 2 Example

- The column I search in is column A (cells A8 to A27; $A$8:$A$27).
- The first row I work with is row 8 (cell $A$8).

The resulting expression (using absolute references) is:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
ROW($A$8:$A$27)-ROW($A$8)+1
```

The image below illustrates how this expression returns the numbers of the rows I work with (the rows of the column I search in), where:

- The number of the first row I work with (row 8) is 1.
- The number of the second row I work with (row 9) is 2.
- …
- The number of the last row I work with (row 27) is 20.

For these purposes:

- Column K (Row Number Base 1) returns the applicable row number.
- Cell L8 displays the formula (using the basic structure of the expression returning the row numbers).

## Step 3: Obtain the Numbers of the Rows Where the Lookup Value is Found

The IF function:

- Checks whether a condition is met (an expression returns TRUE).
- Returns:
- One value if the condition is met (the expression returns TRUE).
- A different value if the condition isn't met (the expression returns FALSE).

Specify the arguments of the IF function as follows when creating a VLookup return multiple values (with the INDEX function) formula:

- Logical_test: The array of TRUEs and FALSEs you created in step #1.
- Value_if_true: The array of row numbers you created in step #2.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
IF(ArrayFromStep1,ArrayFromStep2)
```

When considering the arrays you created in step #1 and step #2:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
IF(LookupValue=ColumnYouSearchIn,ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1)
```

### Step 3 Example

Considering the arrays I created in step #1 and step #2:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
IF($M$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1)
```

The image below illustrates how this expression returns the numbers of the rows where the lookup value is found.

- Column I (IF Function) returns:
- The applicable row number, if the value in column A (Salesperson) matches the lookup value (Salesperson 10).
- FALSE, if the value in column A (Salesperson) doesn't match the lookup value (Salesperson 10).

- Cell J8 displays the formula (using the IF function to return the numbers of the rows where the lookup value is found).

## Step 4: Keep Track of the Number of Results Returned by the VLookup Return Multiple Values (with the INDEX Function) Formula

Work with the ROW function to keep track of the number of results returned by the VLookup return multiple values (with the INDEX function) formula.

To achieve this, do the following:

- In the first cell/row containing the VLookup return multiple values (with the INDEX function) formula, specify the Reference argument of the ROW function as a relative reference to row 1 of the worksheet (1:1).
- When you copy and paste the VLookup return multiple values (with the INDEX function) formula into the subsequent cells/rows (below), Excel adjusts the row references automatically.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
ROW(SequentialNumber:SequentialNumber)
```

### Step 4 Example

The cells storing the VLookup return multiple values (with the INDEX function) formulas are cells J10 to J14.

I specify the Reference argument of the ROW function as follows:

- Cell J10: Row 1 (1:1).
- Cell J11: Row 2 (2:2).
- Cell J12: Row 3 (3:3).
- Cell J13: Row 4 (4:4).
- Cell J14: Row 5 (5:5).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
'Cell J10
ROW(1:1)
'Cell J11
ROW(2:2)
'Cell J12
ROW(3:3)
'Cell J13
ROW(4:4)
'Cell J14
ROW(5:5)
```

## Step 5: Obtain the Row Number Corresponding to the nth Lookup Match

The SMALL function returns the k-th smallest value in a data set.

Specify the arguments of the SMALL function as follows when creating a VLookup return multiple values (with the INDEX function) formula:

- Array: The array (returned by the IF function) you created in step #3, with:
- The numbers of the rows where the lookup value is found; and
- FALSEs.

- K: The number of the match occurrence to return (the nth match), as obtained in step #4.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
SMALL(ArrayFromStep3,MatchOccurrence)
```

When considering:

- The array you created in step #3; and
- The expression you created in step #4.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
SMALL(IF(LookupValue=ColumnYouSearchIn,ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1),ROW(SequentialNumber:SequentialNumber))
```

### Step 5 Example

Considering:

- The array I created in step #3; and
- The expression I created in step #4.

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
'Cell J10
=SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(1:1))
'Cell J11
=SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(2:2))
'Cell J12
=SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(3:3))
'Cell J13
=SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(4:4))
'Cell J14
=SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(5:5))
```

## Step 6: Extract the Value in the Row Where the nth Lookup Match is Found

In the VLookup return multiple values (with the INDEX function) formula, the INDEX function returns the value in a specific row in a single-column (1 column, several rows) cell range.

Specify INDEX's arguments as follows to obtain the value the VLookup return multiple values (with the INDEX function) formula returns:

- Array: The single-column (1 column, several rows) cell range (in the table where you search with the VLookup return multiple values with the INDEX function) with the value to return.
- Row_num: The row number corresponding to the match occurrence to return (the nth match) as returned by the SMALL function. You created this expression in step #5.

The resulting formula template/structure is as follows:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
=INDEX(ColumnWithValueToReturn,RowNumberWithNthMatchOccurrence)
```

When considering the SMALL function you created in step #5:

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
=INDEX(ColumnWithValueToReturn,SMALL(IF(LookupValue=ColumnYouSearchIn,ROW(ColumnYouSearchIn)-ROW(FirstCellInColumnYouSearchIn)+1),ROW(SequentialNumber:SequentialNumber)))
```

### Step 6 Example

The column with the value to return is column H (cells H8 to H27; $H$8:$H$27).

```
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-index/
'Cell J10
=INDEX($H$8:$H$27,SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(1:1)))
'Cell J11
=INDEX($H$8:$H$27,SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(2:2)))
'Cell J12
=INDEX($H$8:$H$27,SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(3:3)))
'Cell J13
=INDEX($H$8:$H$27,SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(4:4)))
'Cell J14
=INDEX($H$8:$H$27,SMALL(IF($K$7=$A$8:$A$27,ROW($A$8:$A$27)-ROW($A$8)+1),ROW(5:5)))
```

## Step 7: Enter the Formula as an Array Formula

The VLookup return multiple values (with the INDEX function) formula template/structure you learned in this Tutorial is an array formula.

If you're not working with Excel 365, you may have to enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

### Step 7 Example

The image below displays the formulas wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter them. The results are the same as those displayed step #6.

## Download the VLookup Return Multiple Values (with the INDEX Function) Example Workbook

This Excel VLookup to return multiple values (with the INDEX function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. **You can get this example workbook (for free) by clicking the button below**.

## Related Excel VLookup Tutorials

This Excel VLookup to return multiple values (with the INDEX function) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to open.