Vlookup - return multiple values in one cell (Concatenate?)

[dixon1983]

Hi,
I have a sheet like the one below:

A B
1 34
1 32
1 20
2 10
2 4
2 9
2 100


I would like to use a vlookup that looks up the value in column A and returns a string of all the values in B. E.g If i was looking up 1 it would return 34 32 20 as one text string.

I'm not too good at Arrays and Indexes etc so don't know what to use to do this.

Any help would be greatly appreciated. Many Thanks in advance.

Dixon

 

[Sour Leaf]

Try this custom function:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
    If r = lookupval Then
        result = result & " " & r.Offset(0, indexcol - 1)
    End If
Next r
MYVLOOKUP = result
End Function

Then type:

=MYVLOOKUP(A1,A1:A20,2)

I know that this has been around a little while but I am hoping that someone can still help.

My Lookup Range has some fields with blank values. This is then showing as a blank space in the cell.

Additionally, I would quite like to enter a '/' instead of a space in between each result. any advice for this?

Many thanks,
Michael

 

[Vecaly]

thanks for the VBA.

how can I put each value from VLOOKUP in different cell?

example: I use MYVLOOKUP on cell A2 and then on cell A2, A3, A4, A5 it will return each value.

best regards.

I have amended the code to fit your needs. I know this was months ago, but someone else maybe looking for the same solution.

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
Dim i As Integer
i = 0
For Each r In lookuprange
    If r = lookupval And i <= 4 Then
        result = result & " " & r.Offset(0, indexcol - 1) & Chr(10)
        i = i + 1
    End If
    Next r
MYVLOOKUP = result
End Function

 

[BiggusDoggus]

Try this custom function:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
    If r = lookupval Then
        result = result & " " & r.Offset(0, indexcol - 1)
    End If
Next r
MYVLOOKUP = result
End Function

Then type:

=MYVLOOKUP(A1,A1:A20,2)

The formula is perfect for my needs, other than - I need the output to still be in one cell, but each result to be on a new line in that cell? What would I need to change please.

EDIT: Ignore sorry, found an updated version

 

[sarad agarwal]

Hi the code above works perfectly fine! There is just one this if you can help me with.

How can i eliminate duplicate values if i have any in my list ?

 

[Sgdva]

This has been covered multiple times in multiple posts, this one that has the link for the best formula for this I've found so far.

 

[JimmyEu]

Hi, Thank you all for the VB script.

i have found that the MYVLOOKUP does not support wildcard in excel "*"&A1&"*". Can anyone help how to make this happen?

 

[Mariuspeens]

HI VenKat - can you also try and help me some VBA code: I have three columns. Column A has the name of the fruit juice, Column B the size of the bottle and Column C the value. I need to match the product with size and add values to a single cell. In the example: = Mango 350ml 7000.000 or Apple 500ml 21620.00

Mango	350ML	R 5,000.00
Mango	350	R 2,000.00
Mango	250	R 3,000.00
Mango	250	R 6,000.00
Mango	125	R 1,200.00
Tropical	125	R 4,000.00
Apple	125	R 8,000.00
Apple	500	R 9,000.00
Apple	500	R 7,000.00
Apple	500	R 5,620.00
Tropical	350	R 3,526.00
Tropical	350	R 2,545.00
Tropical	350	R 5,895.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Sgdva]

HI VenKat - can you also try and help me some VBA code: I have three columns. Column A has the name of the fruit juice, Column B the size of the bottle and Column C the value. I need to match the product with size and add values to a single cell. In the example: = Mango 350ml 7000.000 or Apple 500ml 21620.00

Mango	350ML	R 5,000.00
Mango	350	R 2,000.00
Mango	250	R 3,000.00
Mango	250	R 6,000.00
Mango	125	R 1,200.00
Tropical	125	R 4,000.00
Apple	125	R 8,000.00
Apple	500	R 9,000.00
Apple	500	R 7,000.00
Apple	500	R 5,620.00
Tropical	350	R 3,526.00
Tropical	350	R 2,545.00
Tropical	350	R 5,895.00

<tbody>
</tbody>

This is a different situation -related, but, is not the same-
For this, you have a native solution, "sum if"
Just try to organize your data prior.
Example:
(as you may notice, STD the 350ML for 350 instead and the real Qty for the number

A	B	C	D	Formula	Result
Mango	350	R 5,000.00	5000	=SUMIFS(D:D;A:A;A1;B:B;B1)	7000
Mango	350	R 2,000.00	2000	=SUMIFS(D:D;A:A;A2;B:B;B2)	7000
Mango	250	R 3,000.00	3000	=SUMIFS(D:D;A:A;A3;B:B;B3)	9000
Mango	250	R 6,000.00	6000	=SUMIFS(D:D;A:A;A4;B:B;B4)	9000
Mango	125	R 1,200.00	1200	=SUMIFS(D:D;A:A;A5;B:B;B5)	1200
Tropical	125	R 4,000.00	4000	=SUMIFS(D:D;A:A;A6;B:B;B6)	4000
Apple	125	R 8,000.00	8000	=SUMIFS(D:D;A:A;A7;B:B;B7)	8000
Apple	500	R 9,000.00	9000	=SUMIFS(D:D;A:A;A8;B:B;B8)	21620
Apple	500	R 7,000.00	7000	=SUMIFS(D:D;A:A;A9;B:B;B9)	21620
Apple	500	R 5,620.00	5620	=SUMIFS(D:D;A:A;A10;B:B;B10)	21620
Tropical	350	R 3,526.00	3526	=SUMIFS(D:D;A:A;A11;B:B;B11)	11966
Tropical	350	R 2,545.00	2545	=SUMIFS(D:D;A:A;A12;B:B;B12)	11966
Tropical	350	R 5,895.00	5895	=SUMIFS(D:D;A:A;A13;B:B;B13)	11966

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

 

[Mike Szczesny]

Building upon the formula in post #2, here is a formula for extracting unique items. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E
1	1	a		a	1
2	1	b		b
3	1	c		c
4	1	c		d
5	1	d		j
6	1	a		p
7	1	j		s
8	2	a		n
9	1	p
10	2	r
11	1	s
12	1	s
13	2	t
14	1	n
15	1	a
16
Sheet1

Cell Formulas
Range	Formula
D1	{=IFERROR(INDEX($B$1:$B$15,SMALL(IF(FREQUENCY(IF($A$1:$A$15=$E$1,MATCH($B$1:$B$15,$B$1:$B$15,0)),ROW($B$1:$B$15)-ROW($B$1)+1),ROW($B$1:$B$15)-ROW($B$1)+1),ROWS($D$1:D1)))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chrishaley]

Hi all,

I really need some help, I am not sure if its because its nearly midnight here but I am struggling to adapt the above formulas to get the results I need. I have 2 spreadsheets, one lists customers names and fleet size i.e.

Row Labels	Fleet Size
Customer A	2

<tbody>
</tbody>

I then have another spreadsheet listing the customers and registrations i.e.

Customer	Registration number
Customer A Customer A	Reg 1 Reg 2

<tbody>
</tbody>

What I want is a formula to put into the first sheet which will show me the registrations for that customer but in one cell if possible.

So it looks like the below.

Row Labels	Fleet Size	Registrations
Customer A	2	Reg 1, Reg 2

<tbody>
</tbody>

I would be really grateful if you could help.