Can I use VLookUp if what I am looking up is a range of dates?

Susan Vieira

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?

MCorbin

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

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", "")

Susan Vieira

@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.

MCorbin

LOL - I know the feeling! Sometimes it just takes another pair of eyes

Susan Vieira

The countifs did EXACTLY what I needed it to do! Thank You.