Vlookup - returning multiple vertical values

[seed]

I have a sheet that consists of a column of dates (some repeated) and data
from that particular day. In a 2nd sheet are formatted listings of data for
each day.

In the 2nd sheet, I'm using vlookup to find the data for the first job for
each day. Is there a simple way to manipulate vlookup to return data
corresponding to the 2nd, 3rd, etc occurrence of the date that it is
searching for, or would another function be better suited?

 

[seed]

Is there really no way to do this?

 

[Niek Otten]

There is. It is described here:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

The site suffered from damaged formulas for some time; that's why I was waiting for other replies.
But I just checked and it seems to have been repaired.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is there really no way to do this?
|
| "seed" wrote:
|
| > I have a sheet that consists of a column of dates (some repeated) and data
| > from that particular day. In a 2nd sheet are formatted listings of data for
| > each day.
| >
| > In the 2nd sheet, I'm using vlookup to find the data for the first job for
| > each day. Is there a simple way to manipulate vlookup to return data
| > corresponding to the 2nd, 3rd, etc occurrence of the date that it is
| > searching for, or would another function be better suited?

 

[Lars-Åke Aspelin]

I have a sheet that consists of a column of dates (some repeated) and data
from that particular day. In a 2nd sheet are formatted listings of data for
each day.

In the 2nd sheet, I'm using vlookup to find the data for the first job for
each day. Is there a simple way to manipulate vlookup to return data
corresponding to the 2nd, 3rd, etc occurrence of the date that it is
searching for, or would another function be better suited?

If you have the date used for the search in cell A1 and the number
stating which occurence to look in cell B1for you may try the
following formula

Note: This is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER

=SMALL((rng=A1)*ROW(rng),B1+ROWS(rng)-COUNTIF(rng,A1))

where rng is a named range of your date table in the 2nd sheet, e.g.
Sheet1!A1:A100

The formula returns the row number of the occurence and that can be
used with the INDEX function to find the different data connected to
that occurence.

Example: To find the data in column B of Sheet1 corresponding to a
specific date/occurance use the formula

=INDEX(Sheet1!B:B, "the stuff from above" )

Hope this helps / Lars-Åke

 

[seed]

Thank you both. You've both just solved my problem!