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

 

[SAMCRO2014]

Ok. I will give it a try. I guess I am making it more difficult than it needs to be. Thank you for your help and patience.

 

[SAMCRO2014]

Ok. I gave it a try and I have something wrong with the formula. Can you see the error?


=LOOKUP(B2,'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$B$2:$B$102965/'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$A$2:$A$102965=A2),'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$G$2:$G$102965)

B2 = the date of OT I want to search the annual salary for (WS # 1)

2020.PRI Basic Pay.v1.RXM'!$B$2:$B$102965 = the start date range in pay table (Different file WS # 2)
2020.PRI Basic Pay.v1.RXM'!$A$2:$A$102965 = The employee number range in the pay table (

Different file WS # 2​

)
A2 = the employee number of the date I am searching the annual salary for (WS #1)
2020.PRI Basic Pay.v1.RXM'!$G$2:$G$102965 = Annual salary range in pay table (

Different file WS # 2)​

​

I really appreciate the time and patience you have given me.

 

[FormR]

Can you see the error?

Hi, you are missing an opening parenthesis here.

=LOOKUP(B2,'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$B$2:$B$102965/('[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$A$2:$A$102965=A2),'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$G$2:$G$102965)

 

[SAMCRO2014]

I knew it was something simple! Thanks!!!

 

[SAMCRO2014]

Hi, you are missing an opening parenthesis here.

=LOOKUP(B2,'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$B$2:$B$102965/('[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$A$2:$A$102965=A2),'[2020.PRI Basic Pay.v1.RXM.xlsx]2020.PRI Basic Pay.v1.RXM'!$G$2:$G$102965)

I am getting #N/A as results. I have all the data in the same format. The pay scale table is sorted by Employee number then start date. I have tried to upload sample of the file but it does not like the .xlsx extension.

 

[FormR]

I have tried to upload sample of the file

Hi, you can use a tool found here to upload sample data directly in a reply.

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[SAMCRO2014]

This is the pay scale table:

Book1
	A	B	C	D	E	F	G
1	Employee #	Start Date	End Date	Pay scale group	PS level	Wage Type	Gross Salary amount
2	11111	2019.04.29	9999.12.31	AU-3	1	1001	81525
3	11111	2019.04.29	9999.12.31	AU-3	1	1001	81525
4	11111	2019.04.29	9999.12.31	AU-3	4	1001	90372
5	22222	2018.09.04	2019.03.29	MG-AFS05	9	1002	113721
6	22222	2019.03.30	2019.07.31	MG-AFS05	9	1002	113721
7	22222	2019.03.30	9999.12.31	AU-2	8	1001	91152
8	22222	2019.03.30	9999.12.31	AU-2	8	1001	91152
9	22222	2019.03.30	9999.12.31	AU-3	7	1001	99065
10	22222	2019.08.01	2019.08.30	MG-AFS05	9	1002	113721
11	22222	2019.08.06	2019.08.30	MG-AFS05	9	1001	113721
12	22222	2019.08.31	9999.12.31	MG-AFS05	9	1001	113721
13	44444	2018.09.01	2019.03.29	AU-3	5	1002	93272
14	44444	2018.09.01	2019.03.29	AU-3	7	1002	99065
15	44444	2018.11.21	2019.03.29	AU-3	7	1001	99065
16	44444	2019.03.30	9999.12.31	AU-2	8	1001	91152
17	44444	2019.03.30	9999.12.31	AU-2	8	1001	91152
18	44444	2019.03.30	9999.12.31	AU-3	7	1001	99065
19	44444	2019.07.02	2019.07.12	MG-AFS05	7	1002	105651
20	44444	2019.08.19	2019.09.06	MG-AFS05	7	1002	105651
21	55555	2019.03.16	2019.03.29	AU-4	7	1001	111682
22	55555	2019.03.18	2019.03.29	AU-5	7	1002	122276
23	55555	2019.03.18	2019.03.29	AU-5	7	1002	122276
24	55555	2019.03.30	2019.12.31	AU-5	7	1002	122276
25	55555	2019.03.30	2019.12.31	AU-5	7	1002	122276
26	55555	2019.03.30	9999.12.31	AU-4	7	1001	111682
27	55555	2019.03.30	9999.12.31	AU-4	7	1001	111682
Pay_Scales

This is the data:

Book1
	A	B	C	D	E						K			N	O
1	Employee #	Date	OT Hours	Expanded OT Hours	Wage Text						Annual Salary				Annual Salary Should be
2	11111	2019.05.25	7.5	11.25	O/T 1st Rest @ 1.5 -Paid						#N/A				90,372
3	22222	2019.06.02	5	10	Travel 2ndRest @2.0-Leave						#N/A				99,065
4	44444	2019.05.25	6	9	O/T 1st Rest @ 1.5 -Paid						#N/A				99,065
5	55555	2019.05.27	2	3	Travel Workday @1.5-Leave						#N/A				111,682
6	55555	2019.05.31	7.5	11.25	Travel Workday @1.5-Leave						#N/A				111,682
CAS_PLSR

Cell Formulas
Range	Formula
K2:K6	K2	=LOOKUP(B2,Pay_Scales!$B$2:$B$27/(Pay_Scales!$A$2:$A$27=CAS_PLSR!A2),Pay_Scales!$G$2:$G$27)

 

[FormR]

Hi,

Are your dates proper Excel dates with custom formatting or are they text?

To find out - what do these formula return when placed in a spare cell?
=ISNUMBER(CAS_PLSR!B2)
=ISNUMBER(Pay_Scales!B2)

 

[SAMCRO2014]

Hi,

Are your dates proper Excel dates with custom formatting or are they text?

To find out - what do these formula return when placed in a spare cell?
=ISNUMBER(CAS_PLSR!B2)
=ISNUMBER(Pay_Scales!B2)

I get TRUE for CAS_PLSR and FALSE for Pay_Scales even though I have everything formatted as "General".

 

[FormR]

Hi, I'd have thought it was the other way around! Anyway - I'd suggest to convert the text values to proper dates - one way that would probably work for you is to use the built in find and replace by finding "." and replacing with "-".