Lookup return multiple results

EssKayKay

Board Regular
Is it possible to have a lookup table return numerous answers?

Example:

Lookup Table
5/4/2005 Dog
5/5/2005 Cat
5/5/2005 Mouse
5/6/2005 Pig

Keyin Lookup = 5/5/2005

Returns:
Cat, Mouse

Thanks,
SKK

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

XLGibbs

Well-known Member
Not really, at least not using a look function, and not to return to the same cell...

Care to be more specific on your need? there may be alternative solutions to your dilemma...

Yogi Anand

MrExcel MVP
Hi SKK:

I agree with Gibbs.

One way to do this would be ...
Book2
ABCDE
1LookupTable
205/04/2005Dog05/05/2005
305/05/2005CatCat, Mouse
405/05/2005Mouse
505/06/2005Pig
6
Sheet1

array formula in cell D3 is ...

=MID(MCONCAT(IF(A2:A5<>D2,"",", "&B2:B5)),2,255)

jindon

MrExcel MVP
UDF

1) Alt + F11 to open VB editor
2) [Insert]->[Module] then paste the code
3) click x to close the window

use in cell

=looklike(searchString,Range,columnRef)

if you have list in A1:B4 and searchString in C1 then

=looklike(C1,A1:B4,2)

Code:
``````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``````

vane0326

Well-known Member

=VLOOKUP(D2,A2:B5,2,FALSE)&","&LOOKUP(D2,A2:A5,B2:B5)
Concatenate Vlookup formula.xls
ABCDEF
1LookUp Table
25/4/2005Dog5/5/2005cat ,Moues
35/5/2005cat
45/5/2005Moues
55/6/2005Pig
6
7
Sheet1

vane0326

Well-known Member
This is probably more efficient formula:

=VLOOKUP(D2,A2:B5,2,0)&","&LOOKUP(2,1/(A2:A5=D2),B2:B5)
Concatenate Vlookup formula.xls
ABCDEF
1LookUp Table
25/4/2005Dog5/5/2005cat ,Moues
35/5/2005cat
45/5/2005Moues
55/6/2005Pig
6
Sheet1

Krishnakumar

Well-known Member

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
ABCDEF
1LookUp Table
25/4/2005Dog5/5/2005cat ,Moues
35/5/2005cat
45/5/2005Moues
55/6/2005Pig
6
Sheet1

vane0326,

Is it possible to have a lookup table return numerous answers?

vane0326

Well-known Member
[/quote]

vane0326,

Is it possible to have a lookup table return numerous answers?
[/quote]

Yes your right. I knew its a possibility. Not if you sort it.

Krishnakumar

Well-known Member
Try,
Book1
ABCD
15/4/2005Dog5/5/2005cat
25/5/2005catMoues
35/5/2005MouesCow
45/6/2005Pig
55/5/2005Cow
Sheet4

Formula in D1 and copy down,

=IF(ROWS(\$1:1)<=COUNTIF(\$A\$1:\$A\$5,\$C\$1),INDEX(\$B\$1:\$B\$5,SMALL(IF(\$A\$1:\$A\$5=\$C\$1,ROW(\$B\$1:\$B\$5)),ROWS(\$1:1))),"")

Is there way if 2 words do match then just grab the first text.

either VLOOKUP or INDEX/MATCH

HTH

EssKayKay

Board Regular
Thanks all. I will try this when I get back in the office. I'll let you know how successfull.

Thanks again,
SKK

Replies
5
Views
54
Replies
2
Views
106
Replies
5
Views
129
Replies
1
Views
106
Replies
21
Views
184