Results 1 to 5 of 5

20071218, 05:16 #1
 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 03 Yrs.
Salary Range Exp Range Salary Formula Required
100 03 Yrs 25
200 35 Yrs
300 57 Yrs
400 79 Yrs
500 9+ Yrs
Looking forward to a simple and effective formula.
Thanks
Baiju

20071218, 08:03 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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>03 Years</td><td align=center>3</td><td align=right>100</td><td>03 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>35 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>57 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>79 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.

20071218, 10:14 #3
 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

20071218, 11:56 #4
 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 57 the index starts in 3 and match in 4. I presume that they should all the same. Which gives the correct answer?
Steve

20071218, 12:36 #5
 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