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.
1
-
Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with a table that shows your manually entered desired results. Also consider sharing a sample spreadsheet. There is a blank sheet maker that lets you share safely.– doubleunaryDec 19, 2022 at 20:22
Add a comment
|
3 Answers
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.
-
The
datevalue()function will truncate datetimes to dates only, which indeed is often useful. When all values are dates rather than datetimes,datevalue()is superfluous. See this answer to see why. Dec 20, 2022 at 21:01 -
@doubleunary, thanks for the comment! Any idea why the above won't work on those different formats without DATEVALUE()?– NateDec 20, 2022 at 22:26
-
1Oh, perhaps the values are not dates but text strings that just look like dates. That's indeed a use case for
datevalue(). You can find if the "dates" really are numeric dates with theisdate_strict()function. Dec 20, 2022 at 23:00 -
Ah yeah makes sense, thanks. I didn’t know about isdate_strict() either; appreciate the knowledge!– NateDec 21, 2022 at 0:39
-
The date values are generated by a lookup function from a list of set dates in another tab, so should be date values rather than text strings. However I know the people who'll be using this and would not rule out someone overwriting with a text string that looks like a date, so to have this failsafe built in from the start is advantageous Dec 21, 2022 at 11:13
Try this in Sheet2:
=filter(Sheet1!A2:A, Sheet1!H2:H = D1)
answered Dec 19, 2022 at 20:21
Try with this formula:
=filter(Sheet1!A2:A,D1=Sheet1!H2:H)
