Search code examples
google-sheetsgoogle-sheets-formulavlookup

Google Sheets vlookup cannot find value error on date match

I have two sheets ('Car' and 'Gas Data') in a google sheets document.

Each has dates in column A. When I add a new date in a row in 'Car', a want to autofill the value in column D from the sheet 'Gas Data' with the matching date row.

My failing vlookup: =VLOOKUP(A2,'Ohio Gas Data'!A:D,4,FALSE)

I am getting the error: "Did not find value '44352' in VLOOKUP evaluation." but the value in A2 is a date and I have formatted both columns for dates.

How can I edit the above vlookup to select A2 in 'Car', match it with a date in column A of 'Gas Data', then select the value from Column D?

Solution

  • You may try with this. Even if it's a text or a date with time (meaning that instead of 44352 you have 44352.6012 for example), you can try with:

    =XLOOKUP(DATEVALUE(A2),INDEX(DATEVALUE('Ohio Gas Data'!A:A)),'Ohio Gas Data'!D:D,"No match",0)

    It should round or convert both the column and the value in A2 to make them match. I added an option of "No match" so you have the "feedback" Let me know!