# Check Range for Value (2)

#### excelnovice05

##### Board Regular
Hi Excel Gurus,

I'm looking for a formula that can do the following:

look @ a range of dates
see if the number below any of the ranges cells equals 2
if one does then return 'yes'
if any of the cells in the range are blank and one equals 2, then it returns 'yes'
if none of the cells equals '2' and at least one is blank, then (nothing)
if all of the cells have a value, but none equal 2, then 'no'

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 1 1

cell output = "no"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1 2

cell output = "yes"

10/1/2007 11/1/2007 12/1/2007 1/1/2008
1 1

cell output = (blank)

Thanks in advance for your insights and I look forward to seeing what you can come up with.

=IF(OR(HLOOKUP(C3,G2:J4,2,FALSE)=2,(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+1,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+2,DAY(C3))),G2:J4,2,FALSE)=2),(HLOOKUP((DATE(YEAR(C3),MONTH(C3)+3,DAY(C3))),G2:J4,2,FALSE)=2)),"On Time","")

I've been trying to figure this one out for hours. Only arrived at the following, but couldn't result in blank and the condition of the range which allows for a response only once either a 2 exists or a value is included in the entire range.

Thanks,
The Novice

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Dan Waterloo

##### Well-known Member

=IF(OR(A2=2,B2=2, C2=2,D2=2),"Yes",IF(OR(ISBLANK(A2), ISBLANK(B2), ISBLANK(C2), ISBLANK(D2)),"","No"))

There may be briefer way of writing this.

#### Dan Waterloo

##### Well-known Member
I just realized that your formula includes a possible result of "On Time" whereas your description of the problem didn't include this, so my version may not be complete. I'm assuming the 1's, 2's , and blanks are in cells A2:D2.

#### tafigueroa

##### Board Regular
Assuming the range of 1s, 2s and blanks is in G3 to J3 you can use this....

=IF(MAX(G3:J3)=2,"On Time",IF((COLUMN(J3)-COLUMN(G3)+1)>SUM(G3:J3),"","Late"))

This is assuming any 2 in the sequence is equal to on time and all 1s is equal to late, but you get the idea if you need to use different output text.

Always subtract the furthest to the left column from the furthest from the right column and add 1 to see the number of columns in the series even if there are blanks.

The Count and Counta formulas will not count cells with no value or no formula in them which is why I didn't use either in this case.

#### tafigueroa

##### Board Regular

Sorry...bug in the previous posting use the below instead.

=IF((COLUMN(J3)-COLUMN(G3)+1)>COUNT(G3:J3),"",IF(MAX(G3:J3)=2,"On Time","Late"))

#### excelnovice05

##### Board Regular
Thanks for all of the great feedback. It's truly helpful.

Dan,

I tried your formula and modified it a little as follows:

=IF(OR(HLOOKUP(D3,\$H\$2:\$AJ\$17,2,FALSE)=2,(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2)),"On Time",IF(OR(ISBLANK(HLOOKUP(D3,\$H\$2:\$AJ\$17,2,FALSE)),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE))),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE))),ISBLANK((HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)))),"","No"))

Only one catch...when using the isblank statement it notices there are formulas in the referenced cells and therefore returns 'no'...even though they are blank to the naked eye.

Any thoughts on how to overcome this issue?

Thanks again... I will try the next formula and see where I get.

- The Novice

#### Dan Waterloo

##### Well-known Member

Hi excelnovice05,

Maybe this version will work:
=IF(OR(A2=2,B2=2, C2=2,D2=2),"Yes",IF(OR(A2="", B2="", C2="", D2=""),"","No"))

#### excelnovice05

##### Board Regular
Excellent. It works. One last question. Is there a formula that will return the row a cell lies within? For example, F5, will return only 5?

Below is the formula I have so far...will share the final one once I have it all worked out.

=IF(OR(HLOOKUP(D3,\$H\$2:\$AJ\$17,2,FALSE)=2,(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=2)),"On Time",IF(OR(HLOOKUP(D3,\$H\$2:\$AJ\$17,2,FALSE)="",(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+1,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=""), (HLOOKUP((DATE(YEAR(D3),MONTH(D3)+2,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)=""),(HLOOKUP((DATE(YEAR(D3),MONTH(D3)+3,DAY(D3))),\$H\$2:\$AJ\$17,2,FALSE)="")),"","Over Due"))

Thanks again. This has been very helpful in getting me over the hurdle I was stuck on.

- The Novice

#### excelnovice05

##### Board Regular
Sorry, figured it out. Used "row()". Works like a charm.

-The Novice

#### tafigueroa

##### Board Regular
Seems like a lot of typing to get the same result as you would using the simpler formula of:

=IF((COLUMN(J3)-COLUMN(G3)+1)>COUNT(G3:J3),"",IF(MAX(G3:J3)=2,"On Time","Over Due")) Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,377
Messages
5,836,914
Members
430,462
Latest member
djkiwi1 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back