Assume the lookup table is in the range of E1:F10

A1 = lookup_value

=IF(ROWS($1:1)<=COUNTIF(E$1:E$10,A$1),INDEX(F$1:F$ 10,MATCH(A$1,E$1:E$10,0)+ROWS($1:1)-1),"")

Copied down to a number of cells that is equal to the max count of any
lookup_value. For example, suppose the lookup_value is "white" and it
appears more than any other lookup_value. It appears 10 times so you'd need
to copy the formula to at least 10 cells.

--
Biff
Microsoft Excel MVP


"RBA" wrote in message
...
I sorted my table array by the look up value prior to writing the formula
so
they are grouped together.

"T. Valko" wrote:

Is your table_array sorted on the lookup_value so that the lookup_values
are
all grouped together? Or, are the lookup_values in random locations?

If they're grouped together (sorted) it's a much easier formula. If
they're
random then it's a somewhat complicated array formula.

--
Biff
Microsoft Excel MVP


"RBA" wrote in message
...
I am very familiar with the vlookup function. Is there any way I can
return
multiple cell values if the look up value is located on multiple lines
of
the
table array? In this instance, I'm dealing with text so it's not
something I
can sum.