VLOOKUP and IF - THEN

For simplification purposes I am a smartsheet that has 5 columns in it:

1) Month (this column has each month in a cell ; A1 = Jan, A2 = Feb, A3= March, etc)

2) Date (this column has an exact date in it (i.e. B1= 1/30/23, B2 = 2/20/23, etc)

3) This column has a check box to represent if it is an "Off Month"

4) My current month reference cell (D1 = Jan)

What I am trying to do is in the 5th column cell (E1 for example) I want to do a vlookup in column A to find a match for the current month that is referenced in D1. Once it finds the proper row of information based on a match for the current month then I want to see if there is a check box in column C. If there is I want E1 to say "Off Month". If the checkbox is empty in column C for the current month then I want to populate E1 with the date that is in column B for the current month.

Is there a formula that can be used for this type of situation?

Find more posts tagged with

Accepted answers

David Tutwiler

Yes, but I would use INDEX/MATCH instead which would provide you protection if any of the data moved for any reason. Something like:

=IF(INDEX(A1:C13, MATCH(D1, A1:A13, 0), 3) = true, "Off Month", INDEX(A1:C13, MATCH(D1, A1:A13, 0), 2))

Index match to search the range (columns A through C), and match with the current month (D1). 0 in the match indicates that it is not a sorted list that you are searching. 3 represents the 3rd column to return (ie whether it is an off month or not, here indicated by the checkbox being checked)

If it is checked, then return "Off Month", otherwise, Index/Match again but return the 2nd column this time (the date).

All comments

David Tutwiler

Yes, but I would use INDEX/MATCH instead which would provide you protection if any of the data moved for any reason. Something like:

=IF(INDEX(A1:C13, MATCH(D1, A1:A13, 0), 3) = true, "Off Month", INDEX(A1:C13, MATCH(D1, A1:A13, 0), 2))

If it is checked, then return "Off Month", otherwise, Index/Match again but return the 2nd column this time (the date).

Iastate7653

That worked great! Thanks for your help!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.