# Thread: VLOOKUP multiple results (Custom Function) Thanks:  1 Post #4982369 (1) Likes:  2 Post #4980457 (1)Post #4982369 (1)

1. ## Re: VLOOKUP multiple results (Custom Function)

Originally Posted by FormR
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

2. ## Re: VLOOKUP multiple results (Custom Function)

Col1 col2
Apple 9
Banana 4
Orange 9

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

3. ## Re: VLOOKUP multiple results (Custom Function)

Originally Posted by Juliemax
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:

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```
Excel 2013/2016
ABCDE
1in this text stringcolour lookupfruitcolour
2Apple, banana, orange9, 4, 9apple9
3banana4
4orange9

Sheet1

Worksheet Formulas
CellFormula
B2=MLookUp(A2,D2:E4,2)

4. ## Re: VLOOKUP multiple results (Custom Function)

Originally Posted by DRSteele
Array Formulas
Cell Formula
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)))),""))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
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

5. ## Re: VLOOKUP multiple results (Custom Function)

Originally Posted by allenm518
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/art...3-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?