INDEX and MATCH Function

[SAMCRO2014]

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]

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]

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]

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.

 

[SAMCRO2014]

That worked too!!!

 

[FormR]

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]

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]

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]

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]

Hi, you can still use a formula:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Employee	Start	End	??		Salary		Employee	Date	Salary
2	10264876	13/05/2019	13/08/2019	SP-3 01		47,789		11097280	01/08/2019	103772
3	10718907	29/04/2019	18/10/2019	AU-3 04		90,372		10264876	31/07/2019	47789
4	10753605	01/04/2019	18/10/2019	SP-3 05		52,752
5	11097280	01/04/2019	31/07/2019	MG-AFS05 09		113721
6	11097280	01/08/2019	05/08/2019	MG-AFS05 09		103772
7	11097280	06/08/2019	30/08/2019	MG-AFS05 09		99887
8	11097280	31/08/2019	19/10/2018	MG-AFS05 09		113721
9	12087939	01/04/2019	29/05/2019	AU-3 07		99,065
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=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.