Lookup return multiple results

[EssKayKay]

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

 

[just_jon]

You can also use filters.

Partslookup.xls
	A	B	C	D	E	F
1	Make	year	Model	Part 1	Part2	notes
2	toyota	2005	camary	1909	3003	4cly
3	toyota	2005	camary	1216	3216	1.3
4	honda	2004	accord	1318	4544	4cly
5	toyota	2004	camary	1494	4899	1.6
6	lexus	2003	ex300	2303	4557	6cly
7	cheverolet	2001	ck series	1693	4553	v8
8	honda	2001	civic	1419	4689	1.4
Data

The above [old] sample, with this code<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range, Cancel<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, [A1:F1])<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> [A:F].AutoFilter =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#00007F">Then</SPAN>
        [A:F].AutoFilter
    <SPAN style="color:#00007F">Else</SPAN>
        [A:F].AutoFilter Field:=1
        [A:F].AutoFilter Field:=2
        [A:F].AutoFilter Field:=3
        [A:F].AutoFilter Field:=4
        [A:F].AutoFilter Field:=5
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    Cancel =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

A double click in any cell in A1:F1 toggles filters on/off, then use the filters to select your criteria.

The results can of course be copied to other sheets.

 

[EssKayKay]

Thanks Kris (et al), I think I got it. I'm off till tomorrow but will try in AM.

Thanks again,
sKK

 

[EssKayKay]

Kris,

Is it possible to move all data down one row(i.e., start on row 2 vs. 1)? I'd like column headings in row 1. I've been playing with it but continue to receive errors. This info is actually coming from an Access table. The spreadsheet will be linked to the DB.

Not being much of a programmer leaves me helpless.
Thanks,
SKK

 

[Norie]

SKK

You say this data is coming from Access?

So why not just use a query in Access to return the required results?

If you need to pass some sort of parameter, from say a range, then Excel help details how to do it.

 

[Krishnakumar]

Kris,

Is it possible to move all data down one row(i.e., start on row 2 vs. 1)? I'd like column headings in row 1. I've been playing with it but continue to receive errors. This info is actually coming from an Access table. The spreadsheet will be linked to the DB.

Not being much of a programmer leaves me helpless.
Thanks,
SKK

Try,

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

You could also consider Pivot Table as an alternative.

HTH

 

[Fin Fang Foom]

Try,

Book1
	A	B	C	D
1	5/4/2005	Dog	5/5/2005	cat
2	5/5/2005	cat		Moues
3	5/5/2005	Moues		Cow
4	5/6/2005	Pig
5	5/5/2005	Cow
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

Hi Krishnakumar


I have a similar problem and I used your solution and it works. But if you can, could you break it down piece by piece that how the formula works?

 

[Krishnakumar]

See this

 

[Fin Fang Foom]

Krishnakumar,

I copy your formula down in column D but it did not get me the correct result in cell D6 and D7. It should give me Dog & pig

Sample1.xls
	A	B	C	D	E
1	5/4/2005	Dog	5/5/2005	cat
2	5/5/2005	cat		Moues
3	5/5/2005	Moues		Cow
4	5/4/2005	Pig
5	5/5/2005	Cow
6			5/4/2005
7
8
Sheet1

Now in this example I had to manually adjust the ranges in the formula to get the correct result. Can the formula be modified so all I have to do is copy the formula down from D1?

Sample1.xls
	A	B	C	D	E
1	5/4/2005	Dog	5/5/2005	cat
2	5/5/2005	cat		Moues
3	5/5/2005	Moues		Cow
4	5/4/2005	Pig
5	5/5/2005	Cow
6			5/4/2005	Dog
7				Pig
8
9
10
Sheet2

 

[Krishnakumar]

Hi,

Book5
	A	B	C	D	E
1	5/4/2005	Dog		5/5/2005	5/4/2005
2	5/5/2005	cat		cat	Dog
3	5/5/2005	Moues		Moues	Pig
4	5/4/2005	Pig		Cow
5	5/5/2005	Cow
Sheet1

Does it help you? if you want down the column then a pivot table is the better option.

Formula in D2 and copy across & down,

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

HTH

 

[Fin Fang Foom]

I was going to do that but there are 3000 dates and going across only goes up to 256 columns but the sample I posted is just example. I will look into the pivot table. But if it possible to copy down the formula that will be great.

Is it possible?