VLOOKUP Will NOT Work on my Date Columns

Hello Community,

I am losing it on this simple VLOOKUP formula, for some reason I am not having a value returned on these 2 date columns.

My current formula for one is the following: =VLOOKUP([Site ID]@row, {Cabling Date Range}, 15, 0)

The column is the 15th column in the lookup range but it is saying invalid column value? Any idea why??

My other formula for my other date column is the following: =VLOOKUP([Site ID]@row, {Cut Over Date Range}, 20, 0)

This column is the 20th column in the lookup range but is outputting nothing.

This is becoming frustrating as this should be a simple formula. Someone please help and let me know what is wrong.

Thank you

Find more posts tagged with

Formulas

Accepted answers

All comments

John- Michael Diedrich

I was able to fix the first one by updating the column type to Date. It has been a long day!

Still working on the second formula.

Genevieve P.

Hi @John- Michael Diedrich

I'm glad you were able to figure out the first issue!

For the second formula, is it possible that it's unable to find a match for your Site ID? To test this, try using a COUNTIF to simply COUNT how many times that item appears in your other sheet:

=COUNTIF({Site ID other sheet}, [Site ID]@row)

I would also suggest changing your formulas from a VLOOKUP to an INDEX(MATCH formula. This way you only reference two columns individually instead of on range spanning 20 columns.

A VLOOKUP can break if the columns in that range are swapped around, and it greatly increases your referenced cell limit (even though 18 of those columns aren't being used, they're still being referenced). See: Tips for working with references

An INDEX(MATCH works like this:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case:

=INDEX({Column With Dates}, MATCH([Site ID]@row, {Column with Site ID}, 0))

Let me know if this has helped or if you're still receiving a blank response! If it's still not working, it would be helpful to see some screen captures of both sheets, but please block out sensitive data.

Cheers,

Genevieve

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/332304#Comment_332304

Thank you for the help Genevieve. Unfortunately I am still having no luck after testing.

I tested the COUNTIF to confirm it is in the range, and it returned with 1 so it is. I then tried the INDEX and MATCH formula but received a blank. Picture below:

It's weird because the VLOOKUP is working in the 2 date columns next to it but not in the Cut Over Date column.

Any help is greatly appreciated!

Thanks

Genevieve P.

Hi @John- Michael Diedrich

Thank you for testing! A blank cell indicates that it is finding a Site ID match, but that the cell in your "Cut Over Date" column associated with that ID number is blank. Could that be possible? (Otherwise, it would return a NO MATCH error like your top row).

I also notice that your Site ID column has the values on the Left of the cell (indicating that they're seen as a Text string). Can you check to see if the StoreID column in your other sheet has the values appear in the same way, on the left?

John- Michael Diedrich

https://community.smartsheet.com/discussion/comment/332321#Comment_332321

Thank you Genevieve, it is working now! You were very helpful.

Genevieve P.

I'm glad to hear it! 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.