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

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

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

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.

 Top of Page

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.

 Top of Page

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.

 Top of Page

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.

 Top of Page

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))

 Top of Page