Lookup and match Information based on a date

You could probably do something clever with an OFFSET function by matching the date in the date column, if you're confident that all the data for a given date will be together.

However, the attached uses a different approach to index each entry of the selected date (using a COUNTIF in a new column A, labelled 'Helper Count'); then you just need to use VLOOKUP in your output table to extract the relevant data. I added an 'Item' column to the output to make it easy to see what's going on, but you could equally calculate the number within the VLOOKUP using ROW().
Attachment: Match Information.xls

If you want to get rid of the #N/As at the bottom of the table you need to use IF(ISERROR(VLOOKUP(...)),"-","VLOOKUP(...)). [One of the super additions to Excel 2007 is the IFERROR function, which removes the need to duplicate the VLOOKUP in this way....]

Hi WinteE

For simplicity I put the list and destination cells in the same sheet, to get a working formula

the actual information is stored on a different workbook from the dstination cells and it is hidden

Hi outofthehat

Thanks for the information, my current sheet has a similar set up in it, however the list is getting longer as the information is inserted daily, so I copied the equations all the way down, now it takes a while for the sheet to open and isolate the information when its asked for. I'm looking for a way with less equations to make it work faster by referecning the list diredt

Okay, try this one. For simplicity I've created two separate cells calculating where your selected day starts in the data and how many rows it has (You can bury these inside the formula if you like...). You can then use an OFFSET function to look up each bit of data - so, for example, J2 has this in it:

Please Login or Register  to view this content.

To explain.
1. If the row you're currently in (here, row 2) is bigger than no of rows of data +1 (because of the titles in the output data), then put in a dash. Otherwise do the offset>
2. OFFSET takes min 3 values - cell to start in, how many rows to offset by, how many columns to offset by. So, starting in B1, the top of the 'Route' data, you need to calculate the number of offset rows. I've bracketed them to make them easier to read.
3. Firstly, $I$3-1. I3 shows the row number of the first entry you're interested in, so if you're interested in row 8 but are starting in row 1, then you need to offset by 7 (hence the -1).
4. Secondly, when you copy the formula down in the output data, you need to increment this by 1 for each row that you're copying it down. The first bit of data is in row 2 because of the titles, and that one shouldn't be incremented; the second bit of data is in row 3, and should be incremented by 1; etc. Hence ROW()-2

There are experts on this forum who can give you advice on which should run faster, but unfortunately I'm not one of them!

outofthehat

thats what I needed, it now works a lot quicker, many thanks for your help..

WintE, thanks for your advice, a macro was a possibility I was thinking off is I couldnt get a solution any other way,

Jim