1

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

0

1 Answer 1

2

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.

2
  • doubleunary, this did not work for me, I have attached a sheet with example of what I'm trying to accomplish. Feb 25, 2023 at 22:51
  • 1
    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. In other words, you have bad data. Edited the answer to accommodate. Feb 26, 2023 at 7:56

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.