# Need help checking a cell value versus a table

1. ## Need help checking a cell value versus a table

Below is what I have my excel sheet set up right now. Basically what I want to do is on the "In Range?" section is say Yes or No depending on certain criteria.

So the date I have is for Friday which would be considered for the weekend. I need a function that if its Friday and we made \$5320, to see the range under "Revenue" and pull that that the max hours is 160 And then check that against the Man Hours for the day which was "130" and if its less than the max hours it will say "Yes" in the Range column.

On Monday-Thursday it will pull from the Weekday hours and Friday-Sunday it will pull from Weekend hours.

Please let me know if this is at all possible or it would be too much of a mess. Thank you!

Date RevenueMan Hours In Range? Revenue Weekday Weekend Hours
12/2/2016 Friday 5320 130 ????? 0-1000 70 100
1001-2000 80 110
2001-3000 100 120
3001-4000 130 135
4001-5000 145 150
5001-6000 155 160
6001-7000 165 180
7001-8000 175 185
8001-9000 185 210
9001-12000 200 230
12001-15000 210 250
15001 240 275

rev
maxweekday
maxweekend

2. ## Re: Need help checking a cell value versus a table

Sorry please look at the spreadsheet attached. It did not copy over very well

3. ## Re: Need help checking a cell value versus a table

Try

=IF(AND(WEEKDAY(\$A\$2,2)<=4,\$D\$2<=VLOOKUP(\$C\$2,\$J\$2:\$L\$13,2,1)),"YES",IF(\$D\$2<=VLOOKUP(\$C\$2,\$J\$2:\$L\$13,3,1),"YES",""))

See attached.

4. ## Re: Need help checking a cell value versus a table

John thank you for your help! What would I need to if it does not meet the required range to say "NO" if it is not in range.

And is there a way for you to consider Sunday as a week day. Sunday is usually a slower business day at the company and they consider it a weekday.. Should have mentioned that in my first post.

EDIT: I actually just tested it. It works, but it can't recognize the difference between weekday or weekend for the hours

5. ## Re: Need help checking a cell value versus a table

Try

=IF(\$D2<=VLOOKUP(\$C2,\$J\$2:\$L\$13,INT(WEEKDAY(\$A2,2)/5)+2,1),"YES","NO")

6. ## Re: Need help checking a cell value versus a table

See attached ...

now signing off until 2017!!!

Happy New Year!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1