Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Pick Experience Level (Excel 2003)

    Hi Excel Expert

    I need a formula to pick up the experice range depending on the Salary. The Salary Range and Experience Range are by table, and in the data shee the salary is the current salary. Based on the same it should pick up the Exp range under Formula Required i.e. if salary is 25 which is in the salary range within 100, it should pick up 0-3 Yrs.


    Salary Range Exp Range Salary Formula Required
    100 0-3 Yrs 25
    200 3-5 Yrs
    300 5-7 Yrs
    400 7-9 Yrs
    500 9+ Yrs

    Looking forward to a simple and effective formula.

    Thanks
    Baiju

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pick Experience Level (Excel 2003)

    Assuming that the data (excluding the headings) are in A2:B6, and the salary to be looked up in D2, you can use
    <code>
    =VLOOKUP(D2+100,A2:B6,2)
    </code>
    But this depends on the interval between the salaries being 100.

    Another option is to shift down the salaries one cell and insert a 0, then use INDEX and MATCH:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>1</td><td>Salary Range</td><td>Exp Range</td><td align=right>*</td><td>Salary</td><td>Formula</td><td align=center>2</td><td align=right>0</td><td align=right>*</td><td align=right>*</td><td align=right>25</td><td>0-3 Years</td><td align=center>3</td><td align=right>100</td><td>0-3 Years</td><td align=right>*</td><td align=right>*</td><td>=INDEX(B3:B7,MATCH(D2,A2:A6,1))</td><td align=center>4</td><td align=right>200</td><td>3-5 Years</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=center>5</td><td align=right>300</td><td>5-7 Years</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=center>6</td><td align=right>400</td><td>7-9 Years</td><td align=right>*</td><td align=right>*</td><td align=right>*</td><td align=center>7</td><td align=right>500</td><td>9+ Years</td><td align=right>*</td><td align=right>*</td><td align=right>*</td></table>
    This does not depend on the interval being 100.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pick Experience Level (Excel 2003)

    Hi Hans,

    Thanks for your formula. The intervals are not 100. Thus the index formula would be the right one.

    I tried using the formula in the actual data that i have, but am facing some problem, can you have a look at it and tell me where the problem is. I have highlighted the same in Yellow.

    Regards
    Baiiju
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pick Experience Level (Excel 2003)

    The index formula in C4 starts in Row 4 while the MATCH is also in row 4 while in rows 5-7 the index starts in 3 and match in 4. I presume that they should all the same. Which gives the correct answer?

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pick Experience Level (Excel 2003)

    thanks steve for picking up the error in the formula its working now

Posting Permissions

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