0

I'm trying to create a sheet that looks through 3 other sheets and combines only the data from all 3 that match today's date.

So if these are my sheets

Data 1:
x              y
7/8/2016       Bananas
7/7/2016       Apples

Data 2
x              y
7/8/2016       Oranges
7/7/2016       Grapes

Data 3
x              y
7/8/2016       Pineapple
7/7/2016       Grapefruit

And I need a formula that returns the following result

x
Bananas
Oranges
Pineapple
user1932747
  • 41
  • 1
  • 6
  • 1
    Data 1,2,3 all on the same sheet? what have you tried so far? is your date a string or and excel serial date? – Forward Ed Jul 08 '16 at 20:52
  • I sure hope that all dates on the referenced sheets are true dates and not strings. Otherwise, my below answer will not work. For more information on "serial dates" you might want to read the following: http://stackoverflow.com/questions/38000194/difference-between-date-and-time-w-out-work-week-excel/38001028#38001028 AND http://stackoverflow.com/questions/37100821/change-date-format-using-substitute-or-replace/37101358#37101358 – Ralph Jul 08 '16 at 21:32

1 Answers1

1

Replace Bananas with

=INDEX('Data 1'!B:B,MATCH(Today(),'Data 1'!A:A,0))

Replace Oranges with

=INDEX('Data 2'!B:B,MATCH(Today(),'Data 2'!A:A,0))

and instead of Pineapple you should use

=INDEX('Data 3'!B:B,MATCH(Today(),'Data 3'!A:A,0))

All of the above assumes that the date is in column A of the sheets while the fruits are in column B.

Updated the functions with Today() thanks to @ForwardEd.

Ralph
  • 9,284
  • 4
  • 32
  • 42
  • 1
    I would suggest to use some kind of "helper cell" instead of `Today()` as it is volatile and may lead to a performance loss... – Dirk Reichel Jul 09 '16 at 05:45
  • Hi, Thank you so much for your response. II'm just getting back to this, and it looks like I didn't give a specific enough of an example. I sometimes have multiple rows per date. So it both pineapple and Apple could end up on 7.7/2016, and I need to retrieve both of them. I think right now, your solution just gets the top one, correct? – user1932747 Aug 16 '16 at 19:28