Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: VLOOKUP multiple results (Custom Function)

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP multiple results (Custom Function)

    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!

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    You will need Excel 2016 for this to work because function TEXTJOIN is new; it was designed to do exactly what you ask: take each component of the input cell and concatenate all results at the end, separated by commas. F2 through G6 is the reference table.


    Sources:
    https://www.youtube.com/watch?v=Dn3gbbfH3tU&t=191s
    https://superuser.com/questions/4834...on-in-ms-excel

    B C D E F G
    1 in this text string colour lookup
    2 apple, banana, grape red, yellow, purple fruit colour
    3 banana, yellow apple red
    4 grape purple banana yellow
    5 grape, apple purple, red grape purple
    6 banana, grape yellow, purple kiwi green
    7 kiwi, apple green, red
    8 banana, kiwi, yellow, green
    Sheet58

    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
    Last edited by DRSteele; Jan 4th, 2018 at 06:52 PM.
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    This is perfect! One small adjustment, is there any way to ensure the output cell does not list the same color twice? For example, if the input cell is "Apple, Cherry", can the output say "Red" instead of "Red, Red"? Thanks!

    Quote Originally Posted by DRSteele View Post
    You will need Excel 2016 for this to work because function TEXTJOIN is new; it was designed to do exactly what you ask: take each component of the input cell and concatenate all results at the end, separated by commas. F2 through G6 is the reference table.


    Sources:
    https://www.youtube.com/watch?v=Dn3gbbfH3tU&t=191s
    https://superuser.com/questions/4834...on-in-ms-excel

    B C D E F G
    1 in this text string colour lookup
    2 apple, banana, grape red, yellow, purple fruit colour
    3 banana, yellow apple red
    4 grape purple banana yellow
    5 grape, apple purple, red grape purple
    6 banana, grape yellow, purple kiwi green
    7 kiwi, apple green, red
    8 banana, kiwi, yellow, green
    Sheet58

    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

  4. #4
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    I have no idea how we can extract a unique list of Colours.

    Perhaps you could use a thesaurus to come up with more colours than just "red" - how bout crimson, cardinal, maroon, scarlet? Then again, I have a feeling your actual data has nothing to do with fruits and their hues.
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  5. #5
    Board Regular
    Join Date
    Nov 2013
    Posts
    69
    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
    I have no idea how we can extract a unique list of Colours.

    Perhaps you could use a thesaurus to come up with more colours than just "red" - how bout crimson, cardinal, maroon, scarlet? Then again, I have a feeling your actual data has nothing to do with fruits and their hues.
    Haha you are correct. It's actually for listing teams that are working on a task based on the people assigned to that task. So one column might say "Bob, Steve, Pete" are working on a task, then the next column auto-populated the teams involved based on the teams that Bob, Steve, and Pete belong to. However, if Bob and Steve are both on the HR team I don't want the new column to say "HR, HR", I just want HR listed once.

    Thank you for your help though!

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

    Default Re: VLOOKUP multiple results (Custom Function)

    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.

    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
    Excel 2013/2016
    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



    Worksheet Formulas
    CellFormula
    B2=MLookUp(A2,$D$1:$E$6,2)

    [code]your code[/code]

  7. #7
    Board Regular
    Join Date
    Nov 2013
    Posts
    69
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Thank you so much! This is perfect.

    Quote Originally Posted by FormR View Post
    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.

    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
    Excel 2013/2016
    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

    Worksheet Formulas
    Cell Formula
    B2 =MLookUp(A2,$D$1:$E$6,2)

  8. #8
    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)

    I tried to use this vba code but using numbers instead of colors. And if the numbers is same it doesn't appear. I think it does not allow repitition. I need help :/

  9. #9
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    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
    I tried to use this vba code but using numbers instead of colors.
    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.
    [code]your code[/code]

  10. #10
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VLOOKUP multiple results (Custom Function)

    Well, patience is a virtue. Excel has created a whole host of new functions along with a new calculation engine, spilled arrays and dynamic arrays. You need Excel 365 Insider for the new function UNIQUE to work right here, and you won't need Ctrl+Shift+Enter to invoke it. As far as I understand, in Spring 2019 MS will release this new calculation engine to all users.

    This formula is the above one simply amended with a new function, UNIQUE. It will lookup all the fruits and return all the unique colours.

    Code:
    =TEXTJOIN(", ",1,UNIQUE(IFNA(INDEX($G$3:$G$6,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE($B9,",",REPT(" ",999)),ROW(INDIRECT("1:"&100))*999-998,999)),$F$3:$F$6,0)))),"")))
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

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
  •