Complex Index Match Formula

J

jschalmers

New Member
Joined
Jun 24, 2019
Messages
5
Hi! I'm trying to create an Index Match formula that will look up the employee's job title and base salary in another sheet and match it to the "Job Title - HRIS" and match it with the applicable salary (in range O2:T38), and return the correct Column Header (Y1/Y2/Y3, etc.) from the mini sheet below.

For example, I want to find the "Principal 1" job title and the salary of "64,100" and have the formula return "Y3".

Please help??

Sample.xlsx
ABCDEFGHIJKLMNOPQRST
1Job Title - FullJob Title - HRISOrg Level 1Org Level 2Org Level 3Type of RoleRole Status# Active EmployeesGradeGrade - NumOption MultiplierBase MidpointVPTTCY1Y2Y3Y4Y5Y6
2Accountant 1Acctnt 1Org 10Org 6Org 3Tenurexxxxx$42,300$6,000$48,300$42,300$44,200$46,200$48,100--
3Accountant 2Acctnt 2Org 11Org 4Org 3Tenurexxxxx$42,300$6,000$48,300$42,300$44,200$46,200$48,100--
4Coordinator 1Coord 1Org 5Org 2Org 2Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
5Coordinator 2Coord 2Org 7Org 3Org 2Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
6Coordinator 3Coord 3Org 2Org 1Org 1Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
7Expert 1Expert 1Org 10Org 6Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
8Expert 1Expert 1Org 11Org 4Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
9Expert 1Expert 1Org 8Org 6Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
10Expert 1Expert 1Org 14Org 6Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
11Expert 2Expert 2Org 9Org 6Org 3Tenurexxxxx$42,800$9,000$51,800$39,300$42,800$44,900$46,900--
12Expert 3Expert 3Org 12Org 6Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
13Expert 4Expert 4Org 11Org 4Org 3Tenurexxxxx$48,900$11,500$60,400$42,900$46,900$49,800$52,500$54,600$56,784
14Junior 1Junior 1Org 3Org 1Org 1Tenurexxxxx$41,200$16,000$57,200$41,200$42,900$44,600$46,300$48,100-
15Lead 1Lead 1Org 10Org 6Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
16Lead 1Lead 1Org 8Org 6Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
17Lead 1Lead 1Org 1Org 1Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
18Lead 1Lead 1Org 14Org 6Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
19Lead 2Lead 2Org 9Org 6Org 3Tenurexxxxx$47,300$4,500$51,800$43,800$47,300$49,400$51,400--
20Lead 3Lead 3Org 13Org 5Org 4Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
21Lead 4Lead 4Org 12Org 6Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
22Lead 5Lead 5Org 11Org 4Org 3Tenurexxxxx$53,400$7,000$60,400$47,400$51,400$54,300$57,000--
23Manager 1Mgr 1Org 4Org 3Org 2Tenurexxxxx$41,200$16,000$57,200$41,200$42,900$44,600$46,300$48,100-
24Principal 1Principal 1Org 6Org 3Org 2Tenurexxxxx$69,600$7,500$77,100$58,800$61,500$64,100$66,600$69,600-
25Pro 1Pro 1Org 12Org 6Org 3Tenurexxxxx$42,300$6,000$48,300$42,300$44,200$46,200$48,100--
26Processor 1Processor 1Org 9Org 6Org 3Tenurexxxxx$39,000$5,000$44,000$39,000$40,800$42,500$44,300--
27Specialist 1Spec 1Org 6Org 3Org 2Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
28Specialist 2Spec 2Org 5Org 3Org 2Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
29Specialist 2Spec 2Org 7Org 3Org 2Tenurexxxxx$44,400$5,000$49,400$44,400$45,900$47,400$48,800$50,300-
30Senior Accountant 1Sr Acctnt 1Org 10Org 6Org 3Tenurexxxxx$64,000$11,500$75,500$56,500$58,300$60,200$62,100$64,000$65,900
31Senior Coordinator 1Sr Coord 1Org 5Org 3Org 2Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
32Senior Coordinator 1Sr Coord 1Org 2Org 1Org 1Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
33Senior Coordinator 1Sr Coord 1Org 2Org 1Org 1Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
34Senior Manager 1Sr Mgr 1Org 4Org 3Org 2Tenurexxxxx$51,500$20,000$71,500$46,800$49,000$51,500$53,600$55,800-
35Senior Manager 1Sr Mgr 1Org 3Org 1Org 1Tenurexxxxx$51,500$20,000$71,500$46,800$49,000$51,500$53,600$55,800-
36Senior Specialist 1Sr Spec 1Org 6Org 3Org 2Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
37Senior Specialist 1Sr Spec 1Org 5Org 3Org 2Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
38Senior Specialist 1Sr Spec 1Org 2Org 1Org 1Tenurexxxxx$55,700$6,000$61,700$51,400$53,800$56,600$58,500$60,300-
Sheet1
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try

Pasta1
ABC
1Job Title - HRISSalaryResult
2Principal 164100Y3
Sheet2
Cell Formulas
RangeFormula
C2C2=INDEX(Sheet1!$O$1:$T$1,MATCH(B2,INDEX(Sheet1!$O$2:$T$38,MATCH(A2,Sheet1!$B$2:$B$38,0),0),0))


M.
 
Upvote 0
Solution
Hi Marcelo,

Thanks for the quick reply. The formula throws an error "Value Not Available".
 

Attachments

  • Screen Shot 2021-10-05 at 12.03.27 PM.png
    Screen Shot 2021-10-05 at 12.03.27 PM.png
    236 KB · Views: 8
Upvote 0
It worked perfectly for me
maybe there are extraneous spaces in your data
Check if B24 is exactly equal to Principal 1

And if Q24 is a number and exactly equal to 64100

M.
 
Upvote 0
That looks as though you have the formula on Sheet1 in U2, rather than on Sheet2, like Marcelo showed.
 
Upvote 0
My test

Sheet1
Pasta1
ABCDEFGHIJKLMNOPQRST
1Job Title - FullJob Title - HRISOrg Level 1Org Level 2Org Level 3Type of RoleRole Status# Active EmployeesGradeGrade - NumOption MultiplierBase MidpointVPTTCY1Y2Y3Y4Y5Y6
2Accountant 1Acctnt 1Org 10Org 6Org 3Tenurexxxxx4230060004830042300442004620048100--
3Accountant 2Acctnt 2Org 11Org 4Org 3Tenurexxxxx4230060004830042300442004620048100--
4Coordinator 1Coord 1Org 5Org 2Org 2Tenurexxxxx444005000494004440045900474004880050300-
5Coordinator 2Coord 2Org 7Org 3Org 2Tenurexxxxx444005000494004440045900474004880050300-
6Coordinator 3Coord 3Org 2Org 1Org 1Tenurexxxxx444005000494004440045900474004880050300-
7Expert 1Expert 1Org 10Org 6Org 3Tenurexxxxx489001150060400429004690049800525005460056784
8Expert 1Expert 1Org 11Org 4Org 3Tenurexxxxx489001150060400429004690049800525005460056784
9Expert 1Expert 1Org 8Org 6Org 3Tenurexxxxx489001150060400429004690049800525005460056784
10Expert 1Expert 1Org 14Org 6Org 3Tenurexxxxx489001150060400429004690049800525005460056784
11Expert 2Expert 2Org 9Org 6Org 3Tenurexxxxx4280090005180039300428004490046900--
12Expert 3Expert 3Org 12Org 6Org 3Tenurexxxxx489001150060400429004690049800525005460056784
13Expert 4Expert 4Org 11Org 4Org 3Tenurexxxxx489001150060400429004690049800525005460056784
14Junior 1Junior 1Org 3Org 1Org 1Tenurexxxxx4120016000572004120042900446004630048100-
15Lead 1Lead 1Org 10Org 6Org 3Tenurexxxxx5340070006040047400514005430057000--
16Lead 1Lead 1Org 8Org 6Org 3Tenurexxxxx5340070006040047400514005430057000--
17Lead 1Lead 1Org 1Org 1Org 3Tenurexxxxx5340070006040047400514005430057000--
18Lead 1Lead 1Org 14Org 6Org 3Tenurexxxxx5340070006040047400514005430057000--
19Lead 2Lead 2Org 9Org 6Org 3Tenurexxxxx4730045005180043800473004940051400--
20Lead 3Lead 3Org 13Org 5Org 4Tenurexxxxx557006000617005140053800566005850060300-
21Lead 4Lead 4Org 12Org 6Org 3Tenurexxxxx5340070006040047400514005430057000--
22Lead 5Lead 5Org 11Org 4Org 3Tenurexxxxx5340070006040047400514005430057000--
23Manager 1Mgr 1Org 4Org 3Org 2Tenurexxxxx4120016000572004120042900446004630048100-
24Principal 1Principal 1Org 6Org 3Org 2Tenurexxxxx696007500771005880061500641006660069600-
25Pro 1Pro 1Org 12Org 6Org 3Tenurexxxxx4230060004830042300442004620048100--
26Processor 1Processor 1Org 9Org 6Org 3Tenurexxxxx3900050004400039000408004250044300--
27Specialist 1Spec 1Org 6Org 3Org 2Tenurexxxxx444005000494004440045900474004880050300-
28Specialist 2Spec 2Org 5Org 3Org 2Tenurexxxxx444005000494004440045900474004880050300-
29Specialist 2Spec 2Org 7Org 3Org 2Tenurexxxxx444005000494004440045900474004880050300-
30Senior Accountant 1Sr Acctnt 1Org 10Org 6Org 3Tenurexxxxx640001150075500565005830060200621006400065900
31Senior Coordinator 1Sr Coord 1Org 5Org 3Org 2Tenurexxxxx557006000617005140053800566005850060300-
32Senior Coordinator 1Sr Coord 1Org 2Org 1Org 1Tenurexxxxx557006000617005140053800566005850060300-
33Senior Coordinator 1Sr Coord 1Org 2Org 1Org 1Tenurexxxxx557006000617005140053800566005850060300-
34Senior Manager 1Sr Mgr 1Org 4Org 3Org 2Tenurexxxxx5150020000715004680049000515005360055800-
35Senior Manager 1Sr Mgr 1Org 3Org 1Org 1Tenurexxxxx5150020000715004680049000515005360055800-
36Senior Specialist 1Sr Spec 1Org 6Org 3Org 2Tenurexxxxx557006000617005140053800566005850060300-
37Senior Specialist 1Sr Spec 1Org 5Org 3Org 2Tenurexxxxx557006000617005140053800566005850060300-
38Senior Specialist 1Sr Spec 1Org 2Org 1Org 1Tenurexxxxx557006000617005140053800566005850060300-
Sheet1


Sheet2
Pasta1
ABC
1Job Title - HRISSalaryResult
2Principal 164100Y3
Sheet2
Cell Formulas
RangeFormula
C2C2=INDEX(Sheet1!$O$1:$T$1,MATCH(B2,INDEX(Sheet1!$O$2:$T$38,MATCH(A2,Sheet1!$B$2:$B$38,0),0),0))


As you can see the formula worked

M.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,376
Messages
6,119,175
Members
448,870
Latest member
max_pedreira

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top