INDEX and MATCH Function

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
145
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,219
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,327
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
145
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,219
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
145
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,219
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
145
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,219
Office Version
365
Platform
Windows
Hi, you can still use a formula:

Book1
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,082,334
Messages
5,364,681
Members
400,810
Latest member
elbashka

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top