# 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,082,063
Messages
5,362,961
Members
400,701
Latest member
drs2911

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...