# How to look up a value in a list and return multiple corresponding values

Applies to |
---|

Microsoft Office Excel 2003 |

This article was written by Ashish Mathur, a Microsoft MVP (Most Valuable Professional). Visit the Microsoft MVP Web site for more information. |

In this article, I provide a formula for looking up a value and returning multiple occurrences of the same corresponding value in a list; for example, if in a two-column list, there appear multiple values of the name "Ashish" in the first column, the formula returns all of the corresponding values in the same row in the second column.

- Look up one value and return one corresponding value
- Look up one value and return multiple corresponding values
- Identify row numbers
- Return corresponding values from column B
- Remove possible error values

## Look up one value and return one corresponding value

You use the **VLOOKUP** function to search for a value in the first column of a list and return a value in the same row from another column in the list. Note that the **VLOOKUP** function is designed to return only the corresponding value of the first instance of a lookup value.

For example, if I type "Ashish" in cell A9, and I type the following VLOOKUP formula in B9:

`=VLOOKUP(A9,A1:B7,2,FALSE)`

The **VLOOKUP** function in cell B9 finds the first occurrence of the name "Ashish", and returns the corresponding value in the same row of the second column, which is 234.

## Look up one value and return multiple corresponding values

The formula to look up one value and return multiple corresponding values can be stated as this: Identify the row numbers that contain the name "Ashish" in column A, return the corresponding values from column B, and remove possible error values.

## Identify row numbers

Enter "Ashish" in cell A10. Then, enter the following array formula (CTRL+SHIFT+ENTER) in cell B10 to determine the row number of a corresponding value:

`=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))`

When you enter or fill this formula in subsequent cells, the formula returns the row numbers for each subsequent corresponding value, in this case, 4 and 7.

## Return corresponding values from column B

Now, modify the preceding formula to return the corresponding value (and not the row number) and enter the formula as an array formula (CTRL+SHIFT+ENTER):

`=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)`

The first corresponding value is 234, the value that corresponds to the first occurrence of the name "Ashish".

When you enter or fill this formula in subsequent cells, the formula returns the subsequent corresponding values of 534 and 834.

## Remove possible error values

When you enter or fill this formula in subsequent cells, the formula may return an error value because there are only three occurrences of the name "Ashish" in the first column. To hide the error values, use the **ISERROR** function. Modify the preceding formula and enter it as an array formula (CTRL+SHIFT+ENTER):

`=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))`