VLOOKUP multiple results (Custom Function)

z3115

Board Regular
Joined
Nov 1, 2013
Messages
71
I'm hoping I can create a custom function to help me with a tricky Vlookup type problem:

Let's say my vlookup should return the color of a fruit. I have a table on a hidden sheet that has each fruit with it's corresponding color.

On another sheet, in cell A1 I have multiple fruit separated by commas (e.g. apple, banana, grape).

A vlookup on apple returns "red", a vlookup on banana returns "yellow", etc. What I need is a formula that takes cell A1 as the input and outputs "Red, Yellow, Purple". (basically taking each component of the input cell, running a vlookup on it, and concatenating all results at the end, separated by commas). Also, the number of items in the input cell may vary (in my example there are 3 fruits but it could range from 0 to 6).



Is there ANY sort of way to do this? I know it's sort of weird and specific, but it would help me tremendously. Thanks!
 
Hi, welcome to the forum.

Try to post a small example of what you are looking up, the lookup table and what your expected results are for said example.


Col 1. Col. 2
Apple 9
Banana 4
Orange 9

I would like to to do this
Apple, banana, orange is equals to 9, 4, 9
 
Upvote 0

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Col1 col2
Apple 9
Banana 4
Orange 9

Apple, banana, orange is equals to 9,4,9

Hi, so you want to include duplicate returns. If you have a newer version of Excel with TEXTJOIN() then you can try the formula in post 2 or here is a modified version of the UDF you can try:

VBA Code:
Function MLookUp(lkup, tbl As Range, col As Long)
Dim a As Variant
For Each a In Split(lkup, ",")
    a = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(a) Then MLookUp = MLookUp & ", " & a
Next a
MLookUp = Mid(MLookUp, 3)
End Function

Book1
ABCDE
1in this text stringcolour lookupfruitcolour
2Apple, banana, orange9, 4, 9apple9
3banana4
4orange9
Sheet1
Cell Formulas
RangeFormula
B2B2=MLookUp(A2,D2:E4,2)
 
Last edited by a moderator:
Upvote 0
Array Formulas
CellFormula
C2{=TEXTJOIN(", ",1,IFNA(INDEX($G$3:$G$6,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",999)),ROW(INDIRECT("1:"&100))*999-998,999)),$F$3:$F$6,0)))),""))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Excellent help, thank you for the formula.

I have noticed that if I have more than 33 values in the cell I am searching in, that the formula returns #value . If I delete some values in the cell to get down to 33, then it works. Any workarounds for this limitation? The link supplied for the reference to the formula doesn't give any explanation for the values selected so I'm not sure where to start playing around.

Much appreciated
 
Upvote 0
I have noticed that if I have more than 33 values in the cell I am searching in, that the formula returns #VALUE .

Hi, I think the limitation is the length of the string being returned (32767 chars) versus the number of cells being concatenated.

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE ! error.


Do you think you are likely to be hitting that limitation?
 
Last edited:
Upvote 0
Hi thanks for the UDF
Hi, here is a UDF that you can try;

To use:
1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code below into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like any another worksheet function as demonstrated below.

VBA Code:
Function MLookUp(lkup, tbl As Range, col As Long)
Dim a, b
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each a In Split(lkup, ",")
    b = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(b) Then
      If Not .exists(b) Then .Add b, 1
    End If
  Next a
  MLookUp = Join(.keys, ", ")
End With
End Function

Book1
ABCDE
1in this text stringcolour lookupfruitcolour
2apple, banana, grapered, yellow, purpleapplered
3bananayellowCherryred
4grapepurplebananayellow
5grape, applepurple, redgrapepurple
6Apple, Cherry, Bananared, yellowkiwigreen
7banana, grapeyellow, purple
8kiwi, applegreen, red
9banana, kiwiyellow, green
Sheet1
Cell Formulas
RangeFormula
B2:B9B2=MLookUp(A2,$D$1:$E$6,2)
Hi thanks for this UDF

Can you help me with one more thing?

I have in 1 cell multiple values

A
 
Upvote 0
Hi thanks for this UDF

Can you help me with one more thing?

I have in column 3 multiple values - Is there a way to vlookup all 3(which are in separate line) and return 5,8,9

Column 1Column 2Column 3
A5A
B
C
B8
C9
 
Upvote 0
Can you help me with one more thing?
Hi, you could try this modified version of the UDF from post# 13

VBA Code:
Function MLookUp(lkup, tbl As Range, col As Long)
Dim a As Variant
For Each a In Split(lkup, Chr(10))
    a = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(a) Then MLookUp = MLookUp & "," & a
Next a
MLookUp = Mid(MLookUp, 2)
End Function

Book1
ABCD
1A5A B C5,8,9
2B8
3C9
Sheet1
Cell Formulas
RangeFormula
D1D1=MLookUp(C1,$A$1:$B$3,2)
 
Upvote 0
Super thanks for your help.. Much appreciated :)
Hi, you could try this modified version of the UDF from post# 13

VBA Code:
Function MLookUp(lkup, tbl As Range, col As Long)
Dim a As Variant
For Each a In Split(lkup, Chr(10))
    a = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(a) Then MLookUp = MLookUp & "," & a
Next a
MLookUp = Mid(MLookUp, 2)
End Function

Book1
ABCD
1A5A B C5,8,9
2B8
3C9
Sheet1
Cell Formulas
RangeFormula
D1D1=MLookUp(C1,$A$1:$B$3,2)
 
Upvote 0
Hi, you could try this modified version of the UDF from post# 13

VBA Code:
Function MLookUp(lkup, tbl As Range, col As Long)
Dim a As Variant
For Each a In Split(lkup, Chr(10))
    a = Application.VLookup(Trim(a), tbl, col, 0)
    If Not IsError(a) Then MLookUp = MLookUp & "," & a
Next a
MLookUp = Mid(MLookUp, 2)
End Function

Book1
ABCD
1A5A B C5,8,9
2B8
3C9
Sheet1
Cell Formulas
RangeFormula
D1D1=MLookUp(C1,$A$1:$B$3,2)
Hi really sorry for bothering you again and again but is it possible that I have one cell with the below values

15;18;19;20;54;ABC

And I can return values from a list which has the same values in one column and names in the other(so it basically returns the second column).. I tried using the above MLookUp but it isnt working :(
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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