vlookup - return multiple data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
I sorted my table array by the look up value prior to writing the formula so
they are grouped together.
 
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.
 
Back
Top