Can I use VLookUp if what I am looking up is a range of dates?
I need to cross check an entry in my smartsheet with a range of dates against another sheet. I am able to do a Vlookup for start date (first pic below) but am stumped on how I can get it to look across the range of dates I need (start date to last date). I think I need to use another formula but am unsure on how to express a date range in a find type formula. Not sure if this is making any sense. I added a pic for clarity. So here I am looking for to return an Error if FOB value is 3 or more for the dates 6-Apr-2021 until 9-Apr-2021. On the sheet it is referencing, all dates are in their own row in a date column seen in 2nd pic. Is it possible to do what I am trying to do?
Best Answer
-
Would it need to be any 1 FOB value that is Or would you want it to add the FOBs for all the dates that match?
If you're looking for any 1 FOB in that range that has a 3 or greater, you could try using a formula with a CountIFs statement:
=IF(COUNTIFS({FOB}, >=3, {Date}, >=start@row, {Date}, <=[last date]@row) > 0, "Error", "")
If you would add all the matching FOBs together to see if they add up to more than 3, you could use a Sumifs formula:
=IF(SUMIFS({FOB}, {Date}, >=start@row, {Date}, <=[last date]@row) >= 3, "Error", "")
Answers
-
Would it need to be any 1 FOB value that is Or would you want it to add the FOBs for all the dates that match?
If you're looking for any 1 FOB in that range that has a 3 or greater, you could try using a formula with a CountIFs statement:
=IF(COUNTIFS({FOB}, >=3, {Date}, >=start@row, {Date}, <=[last date]@row) > 0, "Error", "")
If you would add all the matching FOBs together to see if they add up to more than 3, you could use a Sumifs formula:
=IF(SUMIFS({FOB}, {Date}, >=start@row, {Date}, <=[last date]@row) >= 3, "Error", "")
-
@MCorbin I would want to know if at least 1 FOB value in that date range is 3 or above. I will give the COUNTIFS a try, thank you! After staring at it for so long nothing made sense anymore.
-
LOL - I know the feeling! Sometimes it just takes another pair of eyes
-
The countifs did EXACTLY what I needed it to do! Thank You.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!