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

D

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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
 
Sort by date Sort by votes
Lewiy said:
Try this custom function:
Code: 
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:
Code: 
=MYVLOOKUP(A1,A1:A20,2)
Click to expand...

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
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.

scottieboi3000 said:
I have amended the code to fit your needs. I know this was months ago, but someone else maybe looking for the same solution.
Code: 
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
Click to expand...
 
Upvote 0
Lewiy said:
Try this custom function:
Code: 
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:
Code: 
=MYVLOOKUP(A1,A1:A20,2)
Click to expand...

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
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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

[TABLE="width: 413"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Mango[/TD]
[TD="align: right"]350ML[/TD]
[TD] R 5,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]350[/TD]
[TD] R 2,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]250[/TD]
[TD] R 3,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]250[/TD]
[TD] R 6,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]125[/TD]
[TD] R 1,200.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]125[/TD]
[TD] R 4,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]125[/TD]
[TD] R 8,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 9,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 7,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 5,620.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 3,526.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 2,545.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 5,895.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Mariuspeens said:
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

[TABLE="width: 413"]
<tbody>[TR]
[TD]Mango[/TD]
[TD="align: right"]350ML[/TD]
[TD] R 5,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]350[/TD]
[TD] R 2,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]250[/TD]
[TD] R 3,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]250[/TD]
[TD] R 6,000.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD="align: right"]125[/TD]
[TD] R 1,200.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]125[/TD]
[TD] R 4,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]125[/TD]
[TD] R 8,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 9,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 7,000.00[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]500[/TD]
[TD] R 5,620.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 3,526.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 2,545.00[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD="align: right"]350[/TD]
[TD] R 5,895.00[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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
[TABLE="width: 500"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Formula[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]350[/TD]
[TD]R 5,000.00[/TD]
[TD="align: right"]5000[/TD]
[TD]=SUMIFS(D:D;A:A;A1;B:B;B1)[/TD]
[TD="align: right"]7000[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]350[/TD]
[TD]R 2,000.00[/TD]
[TD="align: right"]2000[/TD]
[TD]=SUMIFS(D:D;A:A;A2;B:B;B2)[/TD]
[TD="align: right"]7000[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]250[/TD]
[TD]R 3,000.00[/TD]
[TD="align: right"]3000[/TD]
[TD]=SUMIFS(D:D;A:A;A3;B:B;B3)[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]250[/TD]
[TD]R 6,000.00[/TD]
[TD="align: right"]6000[/TD]
[TD]=SUMIFS(D:D;A:A;A4;B:B;B4)[/TD]
[TD="align: right"]9000[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]125[/TD]
[TD]R 1,200.00[/TD]
[TD="align: right"]1200[/TD]
[TD]=SUMIFS(D:D;A:A;A5;B:B;B5)[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD]125[/TD]
[TD]R 4,000.00[/TD]
[TD="align: right"]4000[/TD]
[TD]=SUMIFS(D:D;A:A;A6;B:B;B6)[/TD]
[TD="align: right"]4000[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]125[/TD]
[TD]R 8,000.00[/TD]
[TD="align: right"]8000[/TD]
[TD]=SUMIFS(D:D;A:A;A7;B:B;B7)[/TD]
[TD="align: right"]8000[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]500[/TD]
[TD]R 9,000.00[/TD]
[TD="align: right"]9000[/TD]
[TD]=SUMIFS(D:D;A:A;A8;B:B;B8)[/TD]
[TD="align: right"]21620[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]500[/TD]
[TD]R 7,000.00[/TD]
[TD="align: right"]7000[/TD]
[TD]=SUMIFS(D:D;A:A;A9;B:B;B9)[/TD]
[TD="align: right"]21620[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]500[/TD]
[TD]R 5,620.00[/TD]
[TD="align: right"]5620[/TD]
[TD]=SUMIFS(D:D;A:A;A10;B:B;B10)[/TD]
[TD="align: right"]21620[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD]350[/TD]
[TD]R 3,526.00[/TD]
[TD="align: right"]3526[/TD]
[TD]=SUMIFS(D:D;A:A;A11;B:B;B11)[/TD]
[TD="align: right"]11966[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD]350[/TD]
[TD]R 2,545.00[/TD]
[TD="align: right"]2545[/TD]
[TD]=SUMIFS(D:D;A:A;A12;B:B;B12)[/TD]
[TD="align: right"]11966[/TD]
[/TR]
[TR]
[TD]Tropical[/TD]
[TD]350[/TD]
[TD]R 5,895.00[/TD]
[TD="align: right"]5895[/TD]
[TD]=SUMIFS(D:D;A:A;A13;B:B;B13)[/TD]
[TD="align: right"]11966[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Building upon the formula in post #2, here is a formula for extracting unique items. Hope this helps.
Mike Szczesny


Excel 2012
ABCDE
11aa1
21bb
31cc
41cd
51dj
61ap
71js
82an
91p
102r
111s
121s
132t
141n
151a
16
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.

[TABLE="width: 326"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Fleet Size[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 333"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Registration number[/TD]
[/TR]
[TR]
[TD]Customer A

Customer A[/TD]
[TD]Reg 1

Reg 2[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 452"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Fleet Size[/TD]
[TD]Registrations[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD]2[/TD]
[TD]Reg 1, Reg 2[/TD]
[/TR]
</tbody>[/TABLE]


I would be really grateful if you could help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Return values from multiple columns based on a list
Replies
6
Views
47
LegendaryAxe
L
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
98
Pingaware
P
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
286
etaf
etaf
D
  • Solved
Lookup a string of text in one sheet and return adjacent value from another sheet
Replies
1
Views
485
arun_eagertolearn
A
S
  • Solved
VLOOKUP with IF or LOOKUP - Which is the right one to use?
Replies
5
Views
312
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,923
Messages
6,175,399
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top