Google Sheets MATCH/VLOOKUP formula searching Date with AM/PM "cannot find value"

Question

I am trying to return a value based on matching dates that have AM/PM. I have tried Index/Match & ArrayFormula & VLookup Formulas that all should have worked to bring in the value that I'm looking for but each time I get "Did not find value '44972.75'" or similar depending on the row. here is the formula

=if($A6<>"",INDEX('On-site Totes Pilot'!$E:$E,MATCH($A6,'On-site Totes Pilot'!$O:$O,0)),"")

Cell A6 Has 2/15/2023 PM in it.
And the Range 'On-site Totes Pilot'!$E:$E also has 2/15/2023 PM in it.

I understand that Google Sheets converts dates to a number but What can I do to make Date 44972.75 match?

Here is a link to a test doc to play with https://docs.google.com/spreadsheets/d/1yDc1fXIH_S6WxsqFGHi9VUW_d4V5aTeaT7fv5c_xR1c/edit?usp=sharing

Answer 1

The error message tells that the value in cell A6 is not the exact date 2023-02-15 but the datetime 2023-02-15 18:00:00, and that none of the values in the column 'On-site Totes Pilot'!O1:O match that datetime exactly.

To match values as they appear in the spreadsheet, use to_text().

The values in 'On-site Totes Pilot!E2:E' are text strings that look like 2/17/2023 PM without a leading zero. The values in 'Chem Usage'!A2:A are numeric date time values that look like 02/17/2023 PM and have a leading zero and two spaces in the middle. To make those inconsistent keys match, use trim() and regexreplace().

To look up multiple values in one go, use vlookup(), like this:

=arrayformula( 
  iferror( 
    vlookup( 
      regexreplace(trim(A2:A100), "^0", ""), 
      { trim('On-site Totes Pilot'!E2:E), 'On-site Totes Pilot'!O2:O }, 
      2, false 
    ) 
  ) 
)

In the sample spreadsheet, the formula goes to cell B2.

See Working with date and time values in Google Sheets.