I appreciate the help. What I'm trying to do is have a summary on another
tab, so I'd have
A
B
C
D
going down the rows. So I'd like to drag the formula down, but I don't want
to have to insert rows between the values of B & C (so only one row per
letter). Is there a way I can copy the formula across columns? Then I can
concatenate each column? Your formula brought in the first value for me, as
you said, but retrieved the error #NUM! for the second value when I dragged
it down one cell.

"Mike H" wrote:

Forgot to mention in the formula E1 is the lookup value

"Mike H" wrote:

Hi,

You could but here's an alternative array formula See below on how to enter

=INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))

This returns the first match and if you drag down 1 row it returns the
second. You could then concatenate the value returned into a single cell and
hide these formula

=F1&","&F2&","&F3
I used F1 - f3 for the array formula

If you want to do it in a single cell then it all gets a bit long winded but
here's a way

=INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$E$1,ROW($A$1: $A$7)-ROW($E$1)+1),ROWS(B$1:B1)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B2)))&","&INDEX($B$1:$B$7,SM ALL(IF($A$1:$A$7=$E$1,ROW($A$1:$A$7)-ROW($E$1)+1),ROWS(B$1:B3)))

This cater for 3 matches but throws an error for less than 3.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



"Kevin W" wrote:

I believe vlookup is set to bring in the first match in the range. But is it
possible to bring in all matches and display them in a list separated by
commas? So:

A Yellow
B Red
C Green
B Blue
D Purple
B Orange
E Black

Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red."

Is there a formula to use that will bring in (Red, Blue, Orange) - all into
D1?? Thanks