Array formula in D4:
Copy cell D4 and paste it down as far as needed.
Array formula in E4:
=INDEX($B$1:$B$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell E4 and paste it down as far as needed.
Download excel file for this tutorial.
Case sensitive vlookup and returning multiple values.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Checks whether two text strings are exactly the same and returns TRUE or FALSE. EXACT is case sensitive.
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.
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference