vlookup question - bring in all values?

[Kevin W]

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

 

[Mike H]

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,SMALL(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,SMALL(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.

 

[Mike H]

Forgot to mention in the formula E1 is the lookup value

 

[Julie]

Mike - I'm sorry I can not get this to work; if looking at the example below

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

And put B in cell E1; it kicks back a #VALUE!

I'm using the same scenario - however I just need it to kick back Blue and
Blue only or the value in cell 'B4' in the example above.

Can you please explain better?

Sorry for the confusion... thank you!

 

[Kevin W]

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.

 

[Jacob Skaria]

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

With your sample data try the below formula
=VLOOKUP_CONCAT(A1:B7,"B",2,", ")

Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn,strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, 1)), _
strLookupValue, vbTextCompare) = 0 Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1)
End Function

If this post helps click Yes

 

[Kevin W]

A little slow for my 2000 rows of data but works perfectly! Thanks!