Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup - returning multiple vertical values
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup - returning multiple vertical values
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup - returning multiple vertical values
There is. It is described he
http://office.microsoft.com/en-us/ex...260381033.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 "seed" wrote in message ... | 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup - returning multiple vertical values
On Tue, 12 Aug 2008 09:11:25 -0700, 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? 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup - returning multiple vertical values
Thank you both. You've both just solved my problem!
"Lars-Ã…ke Aspelin" wrote: On Tue, 12 Aug 2008 09:11:25 -0700, 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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP for returning multiple values and summing them | Excel Worksheet Functions | |||
VLookup returning the sum of multiple values from one "code" | Excel Worksheet Functions | |||
Vlookup - Returning Additional Values | Excel Worksheet Functions | |||
vlookup returning multiple values | Excel Worksheet Functions | |||
vlookup formulas returning no values | Excel Worksheet Functions |