+ Reply to Thread
Results 1 to 6 of 6

Need help checking a cell value versus a table

  1. #1
    Registered User
    Join Date
    08-27-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    14

    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
    Attached Files Attached Files
    Last edited by masonutley; 12-31-2016 at 05:23 PM.

  2. #2
    Registered User
    Join Date
    08-27-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    14

    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. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-27-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Office 2013
    Posts
    14

    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. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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")
    Last edited by JohnTopley; 01-01-2017 at 03:11 AM.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need help checking a cell value versus a table

    See attached ...

    now signing off until 2017!!!

    Happy New Year!!!
    Attached Files Attached Files
    Last edited by JohnTopley; 01-01-2017 at 03:12 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Checking Range, altering out of bounds, checking result against table
    By Sillystring in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-11-2016, 06:47 AM
  2. [SOLVED] IF versus LOOKUP versus ARRAY?
    By Gtrtim112 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-22-2015, 12:55 AM
  3. Looking for statements that will pick off parts of cell versus the whole cell.
    By cadamhill in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 04:50 PM
  4. [SOLVED] 3,000 Tables versus 3,000 Worksheets versus 3,000 Workbooks
    By StevenM in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-18-2012, 03:15 AM
  5. How do I get the macro to go to the last empty cell versus a certain cell location
    By alexfortin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2009, 01:29 PM
  6. Macro menu item versus keyboard shortcut on pivot table
    By TrevorM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2006, 06:50 AM
  7. XLA versus Reference versus Nothing
    By Jos Vens in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2005, 04:06 AM

Bookmarks

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