Salehin31

New Member
hello frndz,

First of all thanx a lot that this forum helped me on every problem I faced earlier.

Now I have a prob.
If salary is equal or less then salary colomn then grade will be on grade colomn

 Grade Salary 335 15,588 336 15,625 337 15,663 338 15,700 339 15,738 340 15,775 341 15,813 342 15,850 343 15,888 344 15,925 345 15,963 346 16,000 347 16,038 348 16,075

<tbody>
</tbody>

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

scopar

New Member
Hi,

I hope I understand what you're after - if the salary was 15,600, you'd want to return a grade of 335. And if salary was 16,040 you'd want to return a grade of 347. Is that correct?

If the grade is in column A and salary is in column B, does the following work:

Code:
`` =INDEX(\$A\$2:\$A\$15,MATCH(E2,\$B\$2:\$B\$15,1))``

I've noticed it causes an error if the salary is lower than the first entry, say 15,000. If that's a problem, maybe another row at the top with salary of 0 and an appropriate entry under grade.

Hope that's what you're after.

Regards,
Scott

Oeldere

Well-known Member
and if you change the columns salary = A, grade = B, you can use Vlookup to do the job for you.

Code:
``I've noticed it causes an error if the salary is lower than the first entry, say 15,000. If that's a problem, maybe another row at the top with salary of 0 and an appropriate entry under grade.``

This comment is the same if you use Vlookup.

scopar

New Member
Hi,

I'm sorry, but I just realised my response wasn't complete. You would have to be a mind reader to make this work. The formula is fine, but the cell reference of E2 (after MATCH) is one that I made up. So, in cell E2 I had a salary value I want to look up:

Code:
`` =INDEX(\$A\$2:\$A\$15,MATCH(E2,\$B\$2:\$B\$15,1))``

Just change that cell reference from E2 to whichever cell contains the salary value you wish to look up. Here's how my sample looks:

Excel 2010
ABCDEF
233515,58815,705338
333615,625
433715,663
533815,700
633915,738
734015,775
834115,813
934215,850
1034315,888
1134415,925
1234515,963
1334616,000
1434716,038
1534816,075

</tbody>
Sheet3

Worksheet Formulas
CellFormula
F2=INDEX(\$A\$2:\$A\$15,MATCH(E2,B2:B15,1))

</tbody>

<tbody>
</tbody>

As also mentioned, the VLOOKUP would work just fine as well if you can put the salary in the first column. With the salary you wish to lookup in cell E2 again, the VLOOKUP formula might be:

Code:
`` =VLOOKUP(E2,\$A\$2:\$A\$15,2,TRUE)``

Using 'TRUE' in the VLOOKUP finds an approximate match rather than an exact match.

My apologies for any confusion ... it had been a long work day yesterday when I responded

Hope this helps.

Regards,
Scott

Salehin31

New Member
Sorry I think I cant express my word properly...

We have a grading sheet to calculate the grade basis of Salary. In our office yearly increment is implement and Salary increased. Now I have to look each employee's grade on grading sheet. I want New salary grading will automatically calculate with Grading Sheet.

Here is some figures of grading sheet
IF the new salary is equal or below 15625 then the grade will b 336
 Grade Salary 335 15,588 336 15,625 337 15,663 338 15,700 339 15,738 340 15,775 341 15,813 342 15,850 343 15,888 344 15,925 345 15,963 346 16,000 347 16,038 348 16,075

<colgroup><col><col></colgroup><tbody>
</tbody>

I say i m dull in English...

Salehin31

New Member
WOWWOWOWOWOWO
Its working...........
Thanx a looooooot..........
God Bless uu...on both life here & there

Replies
2
Views
196

1,195,631
Messages
6,010,785
Members
441,569
Latest member
PeggyLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back