Returning specific values from another spreadsheet based on criteria.

[DBFG18]

I have a spreadsheet where I need to read today's date from it and the corresponding vehicle registrations for today's date into a new spreadsheet in a tidy format.

In it I have a series of days in the month, a column with the date in and then another column with the vehicle reg. I need to take these vehicle registrations if the date is today. On the following day, this should populate with new vehicle registrations for that day etc...

I want the new spreadsheet to look something like this:

DATE REGISTRATIONS
TODAY() JXY120
TBN230
ETC....

I cannot edit the original spreadsheet.

I was thinking I need to do a LOOKUP or something, but I don't know how to return only the vehicle registrations. Plus, the columns which this lookup would need to read from will change every day. For example if Today's date is in column A, then the reg will be in Column B - but then the next day Today's date might be in column F, and thus the reg will be in column G. This is why I am confused on how to do this. The spreadsheet lists every single day in the month, so my formula reading the reg's needs to move to the next day and corresponding vehicle reg column with each passing day.

Can anybody offer any insight?

Thanks in advance.

 

[Special-K99]

I'm assuming the following so base something on this.

Your input spreadsheet is called Sheet1, output spreadsheet is Sheet2
Your input spreadsheet consists of two columns, date and registrations and your data starts at row 2

Put two column headers DATE and REGISTRATIONS in Sheet2 at row 1

in Sheet2!A2
=IFERROR(INDEX(Sheet1!$A$2:$B$1000,AGGREGATE(15,6,ROW(Sheet1!$A$2:$B$1000)/((Sheet1!A$2:$A$1000=TODAY())),ROWS(A$2:A2))-(2-1),COLUMN()),"")
and copy across to column B and down the column for as many rows as you have in Sheet1

Replace the red 2s above with whatever row your actual data (not headers) starts on