Book2 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | LookupTable | ||||||
2 | 05/04/2005 | Dog | 05/05/2005 | ||||
3 | 05/05/2005 | Cat | Cat, Mouse | ||||
4 | 05/05/2005 | Mouse | |||||
5 | 05/06/2005 | Pig | |||||
6 | |||||||
Sheet1 |
Function looklike(x, rng As Range, ref As Integer) As String
Dim r As Range
For Each r In rng
If r = x Then _
looklike = looklike & rng.Cells(r.Row, ref) & ", "
Next
looklike = Left(looklike, Len(looklike) - 2)
End Function
ADVERTISEMENT
Concatenate Vlookup formula.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | LookUp Table | |||||||
2 | 5/4/2005 | Dog | 5/5/2005 | cat ,Moues | ||||
3 | 5/5/2005 | cat | ||||||
4 | 5/5/2005 | Moues | ||||||
5 | 5/6/2005 | Pig | ||||||
6 | ||||||||
7 | ||||||||
Sheet1 |
Concatenate Vlookup formula.xls | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | LookUp Table | |||||||
2 | 5/4/2005 | Dog | 5/5/2005 | cat ,Moues | ||||
3 | 5/5/2005 | cat | ||||||
4 | 5/5/2005 | Moues | ||||||
5 | 5/6/2005 | Pig | ||||||
6 | ||||||||
Sheet1 |
ADVERTISEMENT
vane0326 said:This is probably more efficient formula:
=VLOOKUP(D2,A2:B5,2,0)&","&LOOKUP(2,1/(A2:A5=D2),B2:B5)
Concatenate Vlookup formula.xls
A B C D E F 1 LookUp Table 2 5/4/2005 Dog 5/5/2005 cat ,Moues 3 5/5/2005 cat 4 5/5/2005 Moues 5 5/6/2005 Pig 6 Sheet1
Is it possible to have a lookup table return numerous answers?
[/quote]Is it possible to have a lookup table return numerous answers?
Is there way if 2 words do match then just grab the first text.