Vlookup same value more than once?

[mik_tiff]

I need to look up data in a worksheet from a look up value that occurs more than once.
The look up data is in a table, the first column of the table consists of dates, sorted in ascending order, but each date occurs up to 40 times.
I need to lookup each occurrence of a particular date and return the data in that row.

Eg. in this table how do I look up each occurrence of 2/11/06 and return the data in those rows?

01-Nov-06 0.0326 0.809 -1.4808 0
01-Nov-06 0.0324 0.8104 -1.0092 0
01-Nov-06 0.0318 0.5218 -1.051 0
02-Nov-06 0.0324 0 -1.1286 0
02-Nov-06 0.031 0 -0.976 0
02-Nov-06 0.0164 0 -1.1696 0
03-Nov-06 0 0 -0.9912 0
03-Nov-06 0 0 -0.9826 0
03-Nov-06 0 0 -0.8396 0

Thanks very much
Mick

 

[Peter_SSs]

mik_tiff

Like this?

G5: =COUNTIF(A1:A9,G2)
H2 (copied across and down): =IF(ROWS(H$2:H2)>$G$5,"",INDEX($A$1:$E$9,SMALL(IF($A$1:$A$9=$G$2,ROW($A$1:$A$9)),ROW(1:1)),COLUMNS($G2:H2)))
which must be confirmed with Ctrl+Shift+Enter not just Enter

Mr Excel.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	01-Nov-06	0.0326	0.809	-1.4808	0		Date of Interest
2	01-Nov-06	0.0324	0.8104	-1.0092	0		02-Nov-06	0.0324	0	-1.1286	0
3	01-Nov-06	0.0318	0.5218	-1.051	0			0.031	0	-0.976	0
4	02-Nov-06	0.0324	0	-1.1286	0		No. of Rows	0.0164	0	-1.1696	0
5	02-Nov-06	0.031	0	-0.976	0		3
6	02-Nov-06	0.0164	0	-1.1696	0
7	03-Nov-06	0	0	-0.9912	0
8	03-Nov-06	0	0	-0.9826	0
9	03-Nov-06	0	0	-0.8396	0
10
Find All

 

[mik_tiff]

Thanks Peter

Just the job!

 

[Stormseed]

mik_tiff

Like this?

G5: =COUNTIF(A1:A9,G2)
H2 (copied across and down): =IF(ROWS(H$2:H2)>$G$5,"",INDEX($A$1:$E$9,SMALL(IF($A$1:$A$9=$G$2,ROW($A$1:$A$9)),ROW(1:1)),COLUMNS($G2:H2)))
which must be confirmed with Ctrl+Shift+Enter not just Enter

Man....you are the KING !! AWESOME STUFF !!!

 

[Peter_SSs]

Man....you are the KING !! AWESOME STUFF !!!

Thanks, but this is no great genius on my part - just ideas and techniques picked up mainly from this board.