+ Reply to Thread
Results 1 to 6 of 6

Trying to Nest Formulas LOOKUP and IF

  1. #1
    Registered User
    Join Date
    03-22-2018
    Location
    kaukauna, Wisconsin
    MS-Off Ver
    2016
    Posts
    7

    Trying to Nest Formulas LOOKUP and IF

    Hello,
    I have been going crazy to try and get a formula to do what I want it to do. I've tried nesting different ones and I just am not able to logically tell excel what it is that I want.

    I have attached a mockup of my workbook, there are two tabs. The first tab is a list of JOBS and is fairly self-explanatory. The second tab is a TAX RATE TABLE; first column is the name of the county, second column is an abbreviation of that county name, column C is our state tax rate (all cells are 5%), D is for extra county or stadium tax, E is the date that C + D become effective; and F is the total tax rate C+D depending on the effective date. You can see on the highlighted line, the effective date is 4/1/18, when that rate will become 5.5%.

    All items on Tab 1 are manual, except Column F - Tax Rate
    I want to have column F on Tab 1 filled in automatically from Column F
    Tab 2 for the County Abbreviation typed in Tab 1 column H
    This I can easily do with the formula that I now have in Tab 1 Column F

    However, the tax rate that fills in on Column F needs to be dependent
    upon the Date in Tab 1 Column B as well as the Effective Date in Tab 2 Column E

    If I leave my formula as it is, then on 4/1/18, Tab 1 column F on ALL the
    lines with CAL as the county will change to 5.5%! Only CAL Jobs whose date
    in Tab 1 Column B that are after 4/1/18 should change to 5.5% while
    the CAL jobs in Tab 1 Column B that were before 4/1/18 should remain at 5%
    Attached Files Attached Files
    Last edited by JSelmstar; 03-22-2018 at 01:53 PM. Reason: Changing THread Title

  2. #2
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: Trying to Nest Formulas LOOKUP and IF

    Assuming you will have ONLY ONE line per county
    One thing: you set column F up to only add the two together if Today() is after the date. So a lookup that returned column F would probably work VLOOKUP($H2,'Tax Rates'!$B:$G,5,TRUE)

    However, I added a column with the total (regardless of date) into column F - and have the Total for today (in case you still need it) and use IF(VLOOKUP($H2,'Tax Rates'!$B:$G,4,TRUE)<=$B2,VLOOKUP($H2,'Tax Rates'!$B:$G,5,TRUE),VLOOKUP($H2,'Tax Rates'!$B:$G,2,TRUE))

  3. #3
    Registered User
    Join Date
    03-22-2018
    Location
    kaukauna, Wisconsin
    MS-Off Ver
    2016
    Posts
    7

    Re: Trying to Nest Formulas LOOKUP and IF

    Quote Originally Posted by CRIMEDOG View Post
    Assuming you will have ONLY ONE line per county
    One thing: you set column F up to only add the two together if Today() is after the date. So a lookup that returned column F would probably work VLOOKUP($H2,'Tax Rates'!$B:$G,5,TRUE)

    However, I added a column with the total (regardless of date) into column F - and have the Total for today (in case you still need it) and use IF(VLOOKUP($H2,'Tax Rates'!$B:$G,4,TRUE)<=$B2,VLOOKUP($H2,'Tax Rates'!$B:$G,5,TRUE),VLOOKUP($H2,'Tax Rates'!$B:$G,2,TRUE))
    Crimedog, thank you. However, I just must be dense because I don't understand what you mean. any way you could modify the spreadsheet I attached to my original post so I can see how it works? Thanks

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Trying to Nest Formulas LOOKUP and IF

    Here is CrimeDog's second formula applied to F2:F7 on the Jobs tab.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-22-2018
    Location
    kaukauna, Wisconsin
    MS-Off Ver
    2016
    Posts
    7

    Re: Trying to Nest Formulas LOOKUP and IF

    Quote Originally Posted by JeteMc View Post
    Here is CrimeDog's second formula applied to F2:F7 on the Jobs tab.
    Let us know if you have any questions.
    THANK YOU!!!!! This worked perfectly. I am so grateful for your responses, I am still working hard trying to learn excel but it is very nice to know that this forum is out here if I need help. I appreciate you efforts.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Trying to Nest Formulas LOOKUP and IF

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ 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. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  2. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  3. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  4. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  5. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

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