VLOOKUP multiple results (Custom Function)

z3115

Board Regular
Joined
Nov 1, 2013
Messages
69
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!
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
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/483419/how-to-split-a-string-based-on-in-ms-excel

BCDEFG
1in this text stringcolour lookup
2apple, banana, grapered, yellow, purplefruitcolour
3banana,yellowapplered
4grapepurplebananayellow
5grape, applepurple, redgrapepurple
6banana, grapeyellow, purplekiwigreen
7kiwi, applegreen, red
8banana, kiwi,yellow, green

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet58

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)))),""))}

<thead>
</thead><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>
 
Last edited:

z3115

Board Regular
Joined
Nov 1, 2013
Messages
69
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!

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/483419/how-to-split-a-string-based-on-in-ms-excel

BCDEFG
1in this text stringcolour lookup
2apple, banana, grapered, yellow, purplefruitcolour
3banana,yellowapplered
4grapepurplebananayellow
5grape, applepurple, redgrapepurple
6banana, grapeyellow, purplekiwigreen
7kiwi, applegreen, red
8banana, kiwi,yellow, green

<tbody>
</tbody>
Sheet58

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>
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
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.
 

z3115

Board Regular
Joined
Nov 1, 2013
Messages
69
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!
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,212
Office Version
365
Platform
Windows
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
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">in this text string</td><td style=";">colour lookup</td><td style="text-align: right;;"></td><td style=";">fruit</td><td style=";">colour</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">apple, banana, grape</td><td style="background-color: #FFFF00;;">red, yellow, purple</td><td style="text-align: right;;"></td><td style=";">apple</td><td style=";">red</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">banana</td><td style=";">yellow</td><td style="text-align: right;;"></td><td style=";">Cherry</td><td style=";">red</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">grape</td><td style=";">purple</td><td style="text-align: right;;"></td><td style=";">banana</td><td style=";">yellow</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">grape, apple</td><td style=";">purple, red</td><td style="text-align: right;;"></td><td style=";">grape</td><td style=";">purple</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Apple, Cherry, Banana</td><td style=";">red, yellow</td><td style="text-align: right;;"></td><td style=";">kiwi</td><td style=";">green</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">banana, grape</td><td style=";">yellow, purple</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">kiwi, apple</td><td style=";">green, red</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">banana, kiwi</td><td style=";">yellow, green</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=MLookUp(<font color="Blue">A2,$D$1:$E$6,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

z3115

Board Regular
Joined
Nov 1, 2013
Messages
69
Thank you so much! This is perfect.

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Juliemax

New Member
Joined
Mar 21, 2019
Messages
3
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 :/
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,212
Office Version
365
Platform
Windows
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.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,047
Office Version
365
Platform
Windows
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:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=TEXTJOIN(", ",1,[COLOR=#0000cd]UNIQUE[/COLOR](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)))),"")))[/FONT]
 

Forum statistics

Threads
1,081,423
Messages
5,358,595
Members
400,505
Latest member
JacquiT

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top