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

 

[Krishnakumar]

OK. Try in D1 and copy down,

=IF(ROWS($1:1)+1-MATCH(9.9E+307,$C$1:C1)<=COUNTIF($A$1:$A$5,LOOKUP(9.9E+307,$C$1:C1)),INDEX($B$1:$B$5,SMALL(IF($A$1:$A$5=LOOKUP(9.9E+307,$C$1:C1),ROW($B$1:$B$5)),ROWS($1:1)+1-MATCH(9.9E+307,$C$1:C1))),"")

Note: This is a pretty expensive formula and if you apply this for 300 dates, it would definitely affect the performace of the file.

You could also consider Advanced Filter for this.

HTH

 

[Fin Fang Foom]

Thanks Krishnakumar your formula works.

=IF(ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2)<=COUNTIF($A$1:$A$7000,LOOKUP(9.9E+307,$C$1:C2)),INDEX($B$1:$B$7000,SMALL(IF($A$1:$A$7000=LOOKUP(9.9E+307,$C$1:C2),ROW($B$1:$B$7000)),ROWS($1:2)+1-MATCH(9.9E+307,$C$1:C2))),"")

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

But when I tried to apply your formula to another sheet it gives me a N/A


I notice it does not work if there are dashes ( - ) in the part#.

Why is that?

Sample1.xls
	A	B	C	D
1
2	02-01349	Dog	02-01-349	#N/A
3	407657-0004	Cat		#N/A
4	02-01350	Mouse		#N/A
5	407657-0005	Pig		#N/A
6	02-01351	Cow		#N/A
7			407657-0005	#N/A
8				#N/A
9				#N/A
10
11
Sheet3

Anyone has any ideas?

 

[Fin Fang Foom]

I just can't figuer it out why its not working.

 

[XLGibbs]

When you applied to another sheet, did you copy the CELL or did you use F2 edit mode and copy just the formula...

Did you confirm with CTRL SHIFT ENTER?

 

[Fin Fang Foom]

I select the cell then I went to the formula bar and I copy the formula then I hit esc then I paste the formula into the other worksheet and yes I did confirm CTRL+SHIFT+ENTER. This is very weird. What do you think?

 

[Krishnakumar]

Hi,

Try,

=IF(ROWS($1:1)+1-MATCH("zzzzzzz",$C$2:C2)<=COUNTIF($A$2:$A$6,LOOKUP("zzzzzzz",$C$2:C2)),INDEX($B$1:$B$6,SMALL(IF($A$2:$A$6=LOOKUP("zzzzzzzz",$C$2:C2),ROW($B$2:$B$6)),ROWS($1:1)+1-MATCH("zzzzzzzzzz",$C$2:C2))),"")

I notice it does not work if there are dashes ( - ) in the part#.

9.9E+307 won't work with text values as your part# are text values.

HTH

 

[Fin Fang Foom]

Thanks Krishnakumar it perfect!

 

[Fin Fang Foom]

Your formula works but when I tried to add this into the formula it gives me a error. IF(A2="","",

=IF(A2="","",IF(ROWS(1:2)+1-MATCH(BigStr,$A$1:$A2)<=COUNTIF(Data!$C$1:$C$7000,LOOKUP(BigStr,$A$1:$A2)),INDEX(Data!E$1:E$7000,SMALL(IF(Data!$C$1:$C$7000=LOOKUP(BigStr,$A$1:$A2),ROW(Data!E$1:E$7000)),ROWS(1:2)+1-MATCH(BigStr,$A$1:$A2))),0))

This is the error its giving me #NUM!

Is there a way it could be fixed?

 

[Krishnakumar]

Hi,

Why do you want to add this? In my formula if there are no values to return then the cell remains empty. See the bolded part.

=IF(ROWS($A$1:A1)+1-MATCH("zzzzzzz",$C$2:C2)<=COUNTIF($A$2:$A$6,LOOKUP("zzzzzzz",$C$2:C2)),INDEX($B$1:$B$6,SMALL(IF($A$2:$A$6=LOOKUP("zzzzzzzz",$C$2:C2),ROW($B$2:$B$6)),ROWS($A$1:A1)+1-MATCH("zzzzzzzzzz",$C$2:C2))),"")

HTH

 

[Fin Fang Foom]

I'm sorry I meant "YES"



=IF(A2="","YES",IF(ROWS(1:2)+1-MATCH(BigStr,$A$1:$A2)<=COUNTIF(Data!$C$1:$C$7000,LOOKUP(BigStr,$A$1:$A2)),INDEX(Data!E$1:E$7000,SMALL(IF(Data!$C$1:$C$7000=LOOKUP(BigStr,$A$1:$A2),ROW(Data!E$1:E$7000)),ROWS(1:2)+1-MATCH(BigStr,$A$1:$A2))),0))

How to implement that in the formula?