VLOOKUP multiple results (Custom Function)

z3115 · Jan 4, 2018

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 · Jan 4, 2018

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

	B	C

apple, banana, grape
banana,	apple	red
grape	banana	yellow
grape, apple	grape	purple
banana, grape	kiwi	green
kiwi, apple
banana, kiwi,

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]in this text string[/TD]
[TD="bgcolor: #F8CBAD"]colour lookup[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]red, yellow, purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]fruit[/TD]
[TD="bgcolor: #FFF2CC"]colour[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA"]yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA"]purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA"]purple, red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA"]yellow, purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA"]green, red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA"]yellow, green[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet58

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]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]

z3115 · Jan 5, 2018

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!

DRSteele said:
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

B C D E F G
apple, banana, grape
banana, apple red
grape banana yellow
grape, apple grape purple
banana, grape kiwi green
kiwi, apple
banana, kiwi,

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FCE4D6"]in this text string[/TD]
[TD="bgcolor: #F8CBAD"]colour lookup[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #E2EFDA"]red, yellow, purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]fruit[/TD]
[TD="bgcolor: #FFF2CC"]colour[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #E2EFDA"]yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: #E2EFDA"]purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA"]purple, red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA"]yellow, purple[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA"]green, red[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA"]yellow, green[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet58

[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]

DRSteele · Jan 5, 2018

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 · Jan 8, 2018

DRSteele said:
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 · Jan 8, 2018

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.

VBA 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

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)

z3115 · Jan 8, 2018

Thank you so much! This is perfect.

FormR said:
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
in this text string colour lookup fruit colour
apple, banana, grape apple red
banana yellow Cherry red
grape purple banana yellow
grape, apple purple, red grape purple
Apple, Cherry, Banana red, yellow kiwi green
banana, grape yellow, purple
kiwi, apple green, red
banana, kiwi yellow, green

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: #FFFF00"]red, yellow, purple[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MLookUp(A2,$D$1:$E$6,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Juliemax · Mar 21, 2019

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 · Mar 21, 2019

Juliemax said:
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 · Mar 21, 2019

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.

Excel Formula:

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

VLOOKUP multiple results (Custom Function)

z3115

Board Regular

Excel Facts

DRSteele

Well-known Member

z3115

Board Regular

DRSteele

Well-known Member

z3115

Board Regular

FormR

MrExcel MVP

z3115

Board Regular

Juliemax

New Member

FormR

MrExcel MVP

DRSteele

Well-known Member

Similar threads

Forum statistics

Share this page

VLOOKUP multiple results (Custom Function)

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Board Regular

New Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock