Return the value of cell based from 2 criterias

Hello,

I hope everyone is doing well

I have a sample excel file attached. In sheet named "salary" cell A2 contains an old salary and B2 contains the new salary. What it needs to find is the exact values of A2 and B2 in the other sheet named "Admin scale" and returns the row heading (1 to 11) for level and 1 to 25 for the grade, the value that should return is the highest value which is the new salary.

I hope anyone can assist me. Thank you in advance.

Corine

Remove the #Value and other non numbers from your list and the following code will provide you with a viable solution. If you leave those non-numericals in the columns, the code will fail.

Please Login or Register  to view this content.

On Admin Page

Formula for A13 =MOD(ROW()-2,11)+1
Formula for B13 =OFFSET($A$1,MOD(ROW()-2,11)+1,INT((ROW()-13)/11)+2)
Formula for C13 =INT((ROW()-13)/11)+2

Fill down to row 276 then copy paste Values

Delete Columns D to Z

Formula for C2 to C12 =INT((ROW()-13)/11)+2

Copy Paste Values

This Gives you a nice clean table.

on the Salary Sheet

Formula For C2
=MOD(MAX(IF('Admin Scale'!$B$1:$B$276=$B2,ROW($B$1:$B$276),0))-1,11)

Formula for D2
=INT(MAX(IF('Admin Scale'!$B$1:$B$276=$B2,ROW($B$1:$B$276),0)-1)/11)+1

Please try at

C2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12+'Admin Scale'!$C$2:$Z$12=A2+B2)*'Admin Scale'!$A$2:$A$12,))

D2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12+'Admin Scale'!$C$2:$Z$12=A2+B2)*'Admin Scale'!$C$1:$Z$1,))

Hi Belinda,

Thank you for you quick reply and effort. The formula seems to be working fine in most of the values however , in range A226:B263, the correct result should be Grade 5 Level 10 as per the Admin Scale table. Your formula which gives the result of Level 5, Grade 22 is incorrect as it would return the value 12270.

Hello Bo_Ry

Thank you for your reply and effort formula seems to be working fine however in cell A442:B445, the level should be level 2 (not 4) grade 25 as per the admin scale.

My formula gives 2 and 24 on row 4422-24.png

Originally Posted by belinda200

Hi - so when there is more than one match in your table - what values would you like to be returned?
The same problem in lines 55-56 isnt it? should be 4 and 9....

Hello,

55 and 56 is correct no problem with that. A442:B442, A443:B442 and so on, the correct result should Level 5 Grade 10 (please see the admin scale sheet- cells J6:K6 (J6=7460 & K6=7830) it should match both of the old and new salary from sheet "salary" A226 & B226.

corinereyes. Please update your profile to show the Excel version you are using. Clearly it is Excel 2010 or later, but your profile shows 2003 & 2007.

Hello Boy_Roy,

Yes that is correct. Sorry i was referencing a wrong file. However please see the pic to show the issues. Formula Seems to be working almost good.

Please see Post 11.

Originally Posted by Glenn Kennedy

corinereyes. Please update your profile to show the Excel version you are using. Clearly it is Excel 2010 or later, but your profile shows 2003 & 2007.

thank you .done updating sorry for that

Any thoughts on my solution in Post #3. Have you even tried it?

Please change the Plus sign to ampersand

C2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12&'Admin Scale'!$C$2:$Z$12=A2&B2)*'Admin Scale'!$A$2:$A$12,))

D2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12&'Admin Scale'!$C$2:$Z$12=A2&B2)*'Admin Scale'!$C$1:$Z$1,))

Originally Posted by alansidman

Any thoughts on my solution in Post #3. Have you even tried it?

Hello alansidman,

the code is perfect! i just tried it.. thank you so much for your effort. However , is it not possible with a formula? Instead of code? my colleagues wont understand coding. This issue bothers us since the start of this year :P

Thank you again.

If you attach the code to a Command Button, then all they need to know is "Click On the Button."

Originally Posted by alansidman

If you attach the code to a Command Button, then all they need to know is "Click On the Button."

Hi, yes i did that i will reference this in my future projects. thank you so much!

Originally Posted by Bo_Ry

Please change the Plus sign to ampersand

C2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12&'Admin Scale'!$C$2:$Z$12=A2&B2)*'Admin Scale'!$A$2:$A$12,))

D2
=MAX(INDEX(('Admin Scale'!$B$2:$Y$12&'Admin Scale'!$C$2:$Z$12=A2&B2)*'Admin Scale'!$C$1:$Z$1,))

hi, working perfect! thank you so much for all your efforts. this saves me months of working.