INDEX and MATCH Function

SAMCRO2014

Board Regular
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
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
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
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:

SAMCRO2014

Board Regular
That worked too!!!

FormR

MrExcel MVP
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
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
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
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 Date End Date Group & Level LIA or PRTL Annual Salary 10264876​ 19-05-13 19-08-13 SP-3 01 47,789​ 10718907​ 19-04-29 19-10-18 AU-3 04 90,372​ 10753605​ 19-04-01 19-10-18 SP-3 05 52,752​ 11097280​ 19-04-01 19-07-31 MG-AFS05 09 113721​ 11097280​ 19-08-01 19-08-05 MG-AFS05 09 103772​ 11097280​ 19-08-06 19-08-30 MG-AFS05 09 99887​ 11097280​ 19-08-31 19-10-18 MG-AFS05 09 113721​ 12087939​ 19-04-01 19-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
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.

1,081,474
Messages
5,358,897
Members
400,514
Latest member
JoHio2577

This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...