I need an excel formula

Posted on 2011-10-05
Last Modified: 2012-05-12
I need an excel formula to correctly select the position pay amount when that position number is selected from the drop down on the Projects Tab. The pay amount information is on Sheet1.

I am currently using the following formual to calculate how much based on certain criteria:


Where the 500 is listed is where i need it to be dynamic and based upon what is selected on the drop down.

Ideas and suggestions are welcomed. Please see the attached.
Question by:wrt1mea
    LVL 50

    Assisted Solution


    in your file the value for Position 5 is 104, so the result will be different from the formula with the 500 hard-wired.


    cheers, teylyn
    LVL 41

    Expert Comment

    Try this:


    The VLOOKUP is using the drop down value to seek a result on Sheet1 in E:F, and selecting the 2nd parameter (the amount) with 0-exact match.

    Just extend E1:F15 down if more positions are required in future...

    LVL 50

    Accepted Solution

    I assume both 500s are dynamic as per teylyn's version

    a little simpler to SUM the COUNTIFS and multiply by the VLOOKUP then it doesn't need to repeat....also SUM at the end is redundant.....


    regards, barry
    LVL 1

    Author Closing Comment

    Works great! Thanks for the support!

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now