# Thread: Pick Experience Level (Excel 2003)

1. ## 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. ## 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. ## 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

4. ## 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. ## 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
•