Lookup return multiple results

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
91
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
 

Some videos you may like

Excel Facts

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

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Aug 21, 2004
Messages
16,995
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
Joined
Aug 29, 2004
Messages
819

ADVERTISEMENT

How about this ?


=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
Joined
Aug 29, 2004
Messages
819
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
Joined
Feb 28, 2003
Messages
2,615

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

vane0326,

your formula would fail if there are more than 2 answers.

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

vane0326

Well-known Member
Joined
Aug 29, 2004
Messages
819
[/quote]

vane0326,

your formula would fail if there are more than 2 answers.

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
Joined
Feb 28, 2003
Messages
2,615
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
Joined
Jan 5, 2003
Messages
91
Thanks all. I will try this when I get back in the office. I'll let you know how successfull.

Thanks again,
SKK
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,994
Members
412,633
Latest member
simon_elvin
Top