+ Reply to Thread
Results 1 to 11 of 11

Dynamic Vlookup??

  1. #1
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Dynamic Vlookup??

    I'm having a problem where by I've created a mock tax system, all equations are correct and tested, but..
    I have a column (C) called circumstances, there are 4 different circumstances that can be selected using a data validation drop down list
    I have a separate table with tax rates, formulas for tax rates depending on the circumstances
    when I use vlookup to change the tax owed by a specific person in a specific circumstance the tax owed is only displayed as the value in the look up column (3) and the formula isn't implemented

    How can implement different formulas depending on the choice from the data validation list

    any ideas would be much appreciated

  2. #2
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    I would post the worksheet but apparently newbies are aloud to

  3. #3
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Dynamic Vlookup??

    You can attach a file by clicking Go Advanced, then Manage Attachments. Click Browse to find your file, click Open, then click Upload.

  4. #4
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    i've shaded the problem areas
    Attached Files Attached Files
    Last edited by Duncan96; 04-03-2019 at 04:57 PM.

  5. #5
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    thanks and sorry i posted the wrong one the first time around

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Dynamic Vlookup??

    I did something similar a long time ago. It involves using a quirk of Excel with the Evaluate function and named ranges. I'll see what I can come up with, as your use is a bit different than mine was.

  7. #7
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    Thanks alot,
    we've only had like 8 two hour sessions of excel and my lecturer wants us to create a fully functioning tax system, abit over the top if you ask me

  8. #8
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Dynamic Vlookup??

    Well, the first thing I need to mention is Item 3 from the Forum Guidelines:
    Please Login or Register  to view this content.
    I understand this is most likely related to a class at work, but I think it probably still applies.

    The next thing is, in reviewing your setup, I think you may be seeing some unintended results. Your formulas in E39:E42 reference the Gross Wages in D2 (and D3). I don't think this is what you want, as the formulas will then always be based on those wages. If you can perhaps explain a bit more broadly the end goal, and especially how the math should work, we should be able to help get you there

  9. #9
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    haha my bad, just need help with this problem and I can continue from there

    The end goal would be when I change the circumstance the correlating tax equation would be applied to the gross wage, when I change the circumstance at the moment even if the wage is below the threshold it is still calculated as the max amount

    if i replace the vlookup with the first equation it works a charm but then it doesn't have that dynamic aspect

    I want up to the first 35300 taxed at 20%

    I thought that if i referenced D2 that it would change as I dragged it down the column
    Last edited by Duncan96; 04-03-2019 at 06:08 PM.

  10. #10
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Dynamic Vlookup??

    Try this. Create a named range called TaxFormula (or whatever you want), and set the value to:
    Please Login or Register  to view this content.
    Then replace your vlookup formula with:
    Please Login or Register  to view this content.
    Hopefully that does what you're looking for.

  11. #11
    Registered User
    Join Date
    04-03-2019
    Location
    Ireland
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Vlookup??

    Still getting the same problems but I think I mess around with it some more I think I'm close

    Thanks for your help

+ 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. dynamic vlookup
    By rubz71 in forum Excel General
    Replies: 5
    Last Post: 11-17-2016, 02:04 AM
  2. dynamic vlookup
    By rubz71 in forum Excel General
    Replies: 1
    Last Post: 10-20-2016, 09:33 PM
  3. Dynamic vlookup
    By MikeFranz123 in forum Excel General
    Replies: 3
    Last Post: 04-25-2012, 08:21 AM
  4. Dynamic VLOOKUP
    By Scubades in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-15-2010, 04:48 AM
  5. Dynamic Vlookup
    By excelhelp18 in forum Excel General
    Replies: 1
    Last Post: 06-01-2009, 02:34 AM
  6. dynamic VLookup
    By michaelk68 in forum Excel General
    Replies: 4
    Last Post: 04-10-2009, 06:25 PM
  7. Dynamic VLookUp
    By kaffal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2008, 02:50 PM

Tags for this Thread

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