0

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

3 Answers 3

0

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.

enter image description here

5
  • 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. Commented 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()?
    – Nate
    Commented Dec 20, 2022 at 22:26
  • 1
    Oh, 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 the isdate_strict() function. Commented Dec 20, 2022 at 23:00
  • Ah yeah makes sense, thanks. I didn’t know about isdate_strict() either; appreciate the knowledge!
    – Nate
    Commented Dec 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
    – alphadavid
    Commented Dec 21, 2022 at 11:13
0

Try this in Sheet2:

=filter(Sheet1!A2:A, Sheet1!H2:H = D1)
0

Try with this formula:

=filter(Sheet1!A2:A,D1=Sheet1!H2:H)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.