How can I search another sheet for a name match within a certain date range?

DO NOT REPLY SDGE Anthony Bosco

I'm struggling to create a formula that will not only look for a name match from one sheet to another, but it will only return a match if the date on the row was within the past 6 months.

For finding a name match between sheets, I've been utilizing the following formula:

=IF(CONTAINS(Contractor@row, {Change and Offboarding Master Range 1}), "Yes", "No")

However, I'm unsure how to include a date limitation within this formula or if I should use a different formula?

Please help.

Thank you,

Anthony

Danielle O'Connell

So here's another solution if that one isn't working. You can use a "dummy" column on the sheet with dates for a "yes"/"No" for the last 6 months with this formula: =IF(TODAY(-180) < [DATE COLUMN]@row, "Yes", "No"). From there you could use VLOOKUP in your sheet that you want to do the match on. If you're using names it would look like this:

=VLOOKUP([Contractor Name]@row, {Sheet with the date ranges Range 1}, 3, false) ...the 3 would be however many columns are between the name in the lookup sheet and your dummy column in case you don't use VLOOKUP often.

The issue with this is if you have the same contractor in the list multiple times it will only give you the answer to the first time they show up. If you have something unique - like an order number - that is in both sheets then you wouldn't have this issue and could lookup using that.

I believe there should be a way to still do an IF formula, but it's hard without seeing the sheet to get it just right for the date part to work.

Bassam Khalil

Hi @DO NOT REPLY SDGE Anthony Bosco

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Danielle O'Connell

I personally like using the TODAY function. Depending on where you want the date limitation to show up you could do something like this:

=IF(AND(CONTAINS(Contractor@row, {Change and Offboarding Master Range 1}), TODAY(180)> [THIS SHOULD BE THE COLUMN THE DATE]@row, "Yes","No"))

positive 180 is 6 months after the date. So if in the column you have your dates you have a date that is equal to whatever today is or within 6 months later you will get "Yes" (assuming that the contractor part of the statement is true). If the date is not within 6 months after today's date (IE they are greater than 6 months after or if the date is in the past) it will be "No". Make 180 negative if you want it in the past.

Limitation of this is that the Yes / No answer will always be based on whatever the current date is.

https://help.smartsheet.com/function/today

DO NOT REPLY SDGE Anthony Bosco

@Danielle O'Connell

How would that formula work if I want it to only look for matches from the reference sheet ({Change and Offboarding Master Range 1}) that have been processed in the past 6 months?

I tried to incorporate the formula, but it is still not functioning as I would hope.

Thank you!

Anthony

Danielle O'Connell

So here's another solution if that one isn't working. You can use a "dummy" column on the sheet with dates for a "yes"/"No" for the last 6 months with this formula: =IF(TODAY(-180) < [DATE COLUMN]@row, "Yes", "No"). From there you could use VLOOKUP in your sheet that you want to do the match on. If you're using names it would look like this:

=VLOOKUP([Contractor Name]@row, {Sheet with the date ranges Range 1}, 3, false) ...the 3 would be however many columns are between the name in the lookup sheet and your dummy column in case you don't use VLOOKUP often.

The issue with this is if you have the same contractor in the list multiple times it will only give you the answer to the first time they show up. If you have something unique - like an order number - that is in both sheets then you wouldn't have this issue and could lookup using that.

I believe there should be a way to still do an IF formula, but it's hard without seeing the sheet to get it just right for the date part to work.

DO NOT REPLY SDGE Anthony Bosco

@Danielle O'Connell

This worked great! I also had no idea you could specify how far the column is for the lookup. That's game changing. Thanks so much!