Linda asks in this post: How to return multiple values using vlookup in excel

I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs.

Here is what I have: Data Range is in $E$1:$F$8, I would like my results in Col. B. Lookup value in column A and return the value in Col F that matches.

Since there are duplicates in Col. A I want Col. B to return the next matching value from col. F.

Essentially this is a Vlookup with multiple matches that would return a different value. Thanks for any help you can provide.

Data Range Col. A Col B
Red 2 Red
Green 6 Red
Pink 3 Red
Blue 9 Yellow
Red 7 Blue
Yellow 11 Blue
Blue 4
Red 14

Answer:

Array Formula in cell B1:

=INDEX($F$1:$F$8, SMALL(IF(A1=$E$1:$E$8, ROW($E$1:$E$8)-MIN(ROW($E$1:$E$8))+1, ""), COUNTIF(A1:$A$1, A1))) + CTRL + SHIFT + ENTER

Copy cell B1 and paste it down as far as needed.

Download excel sample file for this tutorial.

Vlookup-next-matching-item.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.
ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text