Sorting Grade with Salary

[Salehin31]

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>

 

[scopar]

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:

 =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]

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

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]

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:

 =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

	A	B	C	D	E	F
1	Grade	Salary			Lookup Value	Grade
2	335	15,588			15,705	338
3	336	15,625
4	337	15,663
5	338	15,700
6	339	15,738
7	340	15,775
8	341	15,813
9	342	15,850
10	343	15,888
11	344	15,925
12	345	15,963
13	346	16,000
14	347	16,038
15	348	16,075

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula F2 =INDEX($A$2:$A$15,MATCH(E2,B2:B15,1)) <thead> </thead><tbody> </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:

 =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]

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]

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