Equivalant to Google Filter Function

I have data concerning classes and dates, times, etc. I need to be able to use a FILTER function in Excel, similar to Google's FILTER but there is not one available. MS says they have had it in BETA for over a year, however no plans on a release date, or if it will be released.

The attached sample shows a very simplified version fo the data I need to filter and the example of what it would need to look like on the end result. I originally was thinking I could use a VLOOKUP command and return data based on TODAY() matching today's date, however this only returns the first entry and there will be multiple entries with the same date. I need all of the entries returned that match today's date. I am looking for some kind of function or formula that can do this automatically, so that after midnight the sheet will update to the data that matches the next day. The end result will be published to a Sharepoint site so I need it to filter and update so that visitors to the site can see the schedule as it pertains to today's date without having to do anything other than just load the sharepoint page. I wsa using GOOGLE Sheets to complete this in the past, however due to increased security requirements our company is switching from Google to MS products. Below is an example of the command that worked in Google sheets:

=FILTER(A2:D,A2:A=TODAY())

A very simple command that filters out and returns only items in the table that match today's date in Column A.

I have an extreme dislike of Pivot Tables and absolutely no knowledge of how they work at all, which is mainly why I don't like them, but from what I have seen they also will not format the table into rows and columns like I want. I found several references to try and use something called Power Query, however I am using O365 on a MAC and it seems to not be available. It keeps saying to go to the DATA tab and select get external data from a Excel sheet, however I do not have an option to get external data from an excel sheet or other sources, only from HTML, Text or New Database Query and no options in the settings to add other sources or anything else to the ribbon so it may be restricted by our IT department, or else it is just not there.

I definitely appreciate any help, if this is even possible. This forum has been a great lifesaver and time saver over the past few days in finding and getting answers on how to do things during this conversion from Google to Excel.

When I click on your link, it says that the site cannot be reached.

Originally Posted by scottiex

maybe this as an array formula

Please Login or Register  to view this content.

confirm with ctrl shift enter

This only returns the date and not the remainder of the columns, it also only returns the first instance and not multiple rows/columns.

Originally Posted by kevinldrbck

This only returns the date and not the remainder of the columns, it also only returns the first instance and not multiple rows/columns.

With a slight modification, I was able to get the desired results with the following formula.

=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$99999=TODAY(),ROW($A$2:$A$99999)),ROWS($A$2:A2))),"")

Entering as an array (SHIFT+CONTROL+ENTER)

Now my next step in the process is getting it to work on a different tab. I have taken the same formula and entered it on a different tab than where the data is at. I changed the cell references to reflect the tab name in the data ranges, however now I do nto get any results. The further modified formula I used s as follows:
=iferror(index('Sheet1'!A:A,small(if('Sheet1'!$A$2:$A$99999=TODAY(),row('Sheet1'!$A$2:$A$99999)),ROWS'Sheet1'!$A$2:A2))),"")

Which returns the results to another sheet. It looks like the final COLUMN command is what was causing only the date to be returned no matter where I placed the data. Thank you for your assistance, this will help out a lot in several different areas of this conversion process.