In Sheet 1 I have a table of orders due for a delivery. Column A is a list of the order numbers, and column H is the delivery date. In Sheet 2, I want to create a list of the order numbers that have a delivery date that is the same as the date in Sheet 2 Cell D1, which already updates itself as required. I have tried a VLookup, but of course this will only return the first value it finds. I have also tried an IFERROR(Filter formula but am struggling to assign the criteria.
Like the other answers have mentioned, the
FILTER() function is a good solution here. However, I would include the use
DATEVALUE() for robustness to different date formats. For example, try this:
=FILTER(Sheet1!A2:A, DATEVALUE(Sheet1!H2:H) = DATEVALUE(D1))
Below is an example of this formula on a sheet with various date field formats in both the variable date and the dates in the order list, where the order values that should match are highlighted orange, the variable date in blue and the filter results in green. You can see the actual formula used in the screenshot.