INDEX and MATCH Function

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
141
I am trying to use the INDEX and MATCH function to look up a date within a range to bring back a annual salary amount. Here is the LOOKUP table which is on a sperate tab in the workbook call Pay_Scales:


Annual Salary Start Date End Date
46395 2018-12-17 2019-05-02
47789 2019-05-03 2019-05-13
50000 2019-05-14 9999-12-31

I am trying to find the salary amount on 2019-07-13 which should be 50K but I am getting #N/A.

Here is my formula:

{=INDEX(Pay_Scales!$A$2:$C$4,MATCH(1,IF(B2>=Pay_Scales!A2:A4,IF(CAS_PLSR!B2<=Pay_Scales!B2:B4,1))),0)}

Can you see my issue? Also, it is possible to put this formula into a loop?

Thanks
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,151
Office Version
365
Platform
Windows
Hi shouldn't it be?

{=INDEX(Pay_Scales!$A$2:$C$4,MATCH(1,IF(B2>=Pay_Scales!B2:B4,IF(CAS_PLSR!B2<=Pay_Scales!C2:C4,1))),1)}
But I think you can just use:

=LOOKUP(B2,Pay_Scales!B2:B4,Pay_Scales!A2:A4)
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,302
Does this work?

=LOOKUP(2,1/((Pay_Scales!B$2:B$4<="2019-07-13")*(Pay_Scales!C$2:C$4>="2019-07-13")),Pay_Scales!A$2:A$4)
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
141
Hi shouldn't it be?



But I think you can just use:

=LOOKUP(B2,Pay_Scales!B2:B4,Pay_Scales!A2:A4)
YAHOO!! That worked. Now can I incorporate this into a loop? I want the loop to go through all the employee numbers and corresponding dates looking for the Employee number and the date ranges for that employee number on a difference WS and bring back the annual salary. Employee number and date are on WS CAS_PLSR and the look up table is on WS PAY_Scales.
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,151
Office Version
365
Platform
Windows
Now can I incorporate this into a loop?
Hi, you typically use loops in code, with a formula you typically copy it to the cells where you want to return the result.
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
141
Hi, you typically use loops in code, with a formula you typically copy it to the cells where you want to return the result.

I am writing this for a macro. It is part of the coding. Should I be looking at this different?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,151
Office Version
365
Platform
Windows
Hi, I'm not sure you have really told us enough to make any code suggestions, but have you considered having your code apply for the formula to a range of cells and then convert those formulas to values rather than looping?
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
141
Ok. Let me try to explain this. I have a listing of employee numbers. Each employee number has different date ranges depending on their annual salary at the time. For example:

EE # Start DateEnd DateGroup & LevelLIA or PRTLAnnual Salary
10264876
19-05-1319-08-13 SP-3 01
47,789
10718907
19-04-2919-10-18 AU-3 04
90,372
10753605
19-04-0119-10-18 SP-3 05
52,752
11097280
19-04-0119-07-31MG-AFS05 09
113721
11097280
19-08-0119-08-05MG-AFS05 09
103772​
11097280
19-08-0619-08-30MG-AFS05 09
99887​
11097280
19-08-3119-10-18MG-AFS05 09
113721
12087939
19-04-0119-05-29 AU-3 07
99,065


I am trying to estimate the OT earned on a certain date by certain employees. I have a single date for the overtime earned and need macro to bring back the annual salary. I need the macro to go through each employee on my list and search for a date within a range (such as above) and bring back the annual salary. I have over 850 employees and each employee can have up to 7 date ranges each.

Clear as mud?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,151
Office Version
365
Platform
Windows
Hi, you can still use a formula:

xl2bb.xlam
ABCDEFGHIJ
1EmployeeStartEnd??SalaryEmployeeDateSalary
21026487613/05/201913/08/2019SP-3 0147,7891109728001/08/2019
31071890729/04/201918/10/2019AU-3 0490,3721026487631/07/2019
41075360501/04/201918/10/2019SP-3 0552,752
51109728001/04/201931/07/2019MG-AFS05 09113721
61109728001/08/201905/08/2019MG-AFS05 09103772
71109728006/08/201930/08/2019MG-AFS05 0999887
81109728031/08/201919/10/2018MG-AFS05 09113721
91208793901/04/201929/05/2019AU-3 0799,065
Sheet1
Cell Formulas
Range(s)Formula
J2:J3J2=LOOKUP(I2,$B$2:$B$9/($A$2:$A$9=H2),$F$2:$F$9)


If you really want to use a macro you can apply the formula to a range of cells and convert to values without needing to loop.
 

Forum statistics

Threads
1,077,985
Messages
5,337,545
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top