Lookup return multiple results

EssKayKay

Board Regular
Joined
Jan 5, 2003
Messages
216
Office Version
  1. 2007
Platform
  1. Windows
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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...
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
[/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.
 
Upvote 0
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
 
Upvote 0
Thanks all. I will try this when I get back in the office. I'll let you know how successfull.

Thanks again,
SKK
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top