VLOOKUP multiple results (Custom Function)

[z3115]

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!

 

[pritishk]

I also tried replace Chr(10) with Chr(59) which is basically the syntax for a semicolon but no luck
Am I doing something wrong here..

 

[FormR]

Hi, you can try like this.

Function MLookUp(lkup, tbl As Range, col As Long)
Dim a As Variant
For Each a In Split(lkup, ";")
    a = Application.VLookup(IIf(IsNumeric(a), Val(a), a), tbl, col, 0)
    If Not IsError(a) Then MLookUp = MLookUp & "," & a
Next a
MLookUp = Mid(MLookUp, 2)
End Function

 

[pritishk]

You are a life saver!