VLOOKUP multiple results (Custom Function)
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: VLOOKUP multiple results (Custom Function)

  1. #11
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Quote Originally Posted by FormR View Post
    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. #12
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Col1 col2
    Apple 9
    Banana 4
    Orange 9

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

  3. #13
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Quote Originally Posted by Juliemax View Post
    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)

    [code]your code[/code]

  4. #14
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Quote Originally Posted by DRSteele View Post
    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. #15
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,063
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Quote Originally Posted by allenm518 View Post
    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?
    Last edited by FormR; Aug 19th, 2019 at 03:15 AM.
    [code]your code[/code]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •