VLOOKUP multiple results (Custom Function)

[z3115]

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!

 

[Juliemax]

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

 

[Juliemax]

Col1 col2
Apple 9
Banana 4
Orange 9

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

 

[FormR]

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:

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
	A	B	C	D	E
1	in this text string	colour lookup		fruit	colour
2	Apple, banana, orange	9, 4, 9		apple	9
3				banana	4
4				orange	9
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MLookUp(A2,D2:E4,2)

 

[allenm518]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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)))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[FormR]

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?

 

[pritishk]

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.

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
	A	B	C	D	E
1	in this text string	colour lookup		fruit	colour
2	apple, banana, grape	red, yellow, purple		apple	red
3	banana	yellow		Cherry	red
4	grape	purple		banana	yellow
5	grape, apple	purple, red		grape	purple
6	Apple, Cherry, Banana	red, yellow		kiwi	green
7	banana, grape	yellow, purple
8	kiwi, apple	green, red
9	banana, kiwi	yellow, green
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=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

 

[pritishk]

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 1	Column 2	Column 3
A	5	A B C
B	8
C	9

 

[FormR]

Can you help me with one more thing?

Hi, you could try this modified version of the UDF from post# 13

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
	A	B	C	D
1	A	5	A B C	5,8,9
2	B	8
3	C	9
Sheet1

Cell Formulas
Range	Formula
D1	D1	=MLookUp(C1,$A$1:$B$3,2)

 

[pritishk]

Super thanks for your help.. Much appreciated

Hi, you could try this modified version of the UDF from post# 13

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
	A	B	C	D
1	A	5	A B C	5,8,9
2	B	8
3	C	9
Sheet1

Cell Formulas
Range	Formula
D1	D1	=MLookUp(C1,$A$1:$B$3,2)

 

[pritishk]

Hi, you could try this modified version of the UDF from post# 13

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
	A	B	C	D
1	A	5	A B C	5,8,9
2	B	8
3	C	9
Sheet1

Cell Formulas
Range	Formula
D1	D1	=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