Lookup Values Column C

Lookup Range Column A

Return Range Column B

Resulting Values I want Column D

- Code: Select all Expand viewCollapse view
`A B C D`

Person Colour Person Colours

Bob Red Adam Red

Adam Red Bob Red,Green,Blue

Bob Green Peter Blue,Yellow

Bob Blue

Peter Blue

Peter Yellow

Found a UDF written for Excel that looked like it might work but I get an error on the line

"temp = IIf(cell = val, temp & rtn(i) & delim, temp)" Inadmissible value or data type.

Index out of defined range.

- Code: Select all Expand viewCollapse view
`REM ***** BASIC`

Function TEXTIF(val As Variant, rng As Range, rtn As Range, Optional delim As String) As String

'https://www.reddit.com/u/excelevator

'https://old.reddit.com/r/excelevator

'https://www.reddit.com/r/excel - for all your Spreadsheet questions!

'TEXTIF ( LOOKUP_VALUE , LOOKUP_RANGE , RETURN_RANGE , [OPTIONAL] DELIMITER )

If IsEmpty(delim) Then

delim = ""

End If

Dim temp As String

Dim i As Integer

i = 1

For Each cell In rng

temp = IIf(cell = val, temp & rtn(i) & delim, temp)

i = i + 1

Next

TEXTIF = Left(temp, Len(temp) - Len(delim))

End Function