INDEX and MATCH Function

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
145
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

Board Regular
Joined
Sep 3, 2015
Messages
145
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Can you see the error?
Hi, you are missing an opening parenthesis here.

Rich (BB code):
=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

Board Regular
Joined
Sep 3, 2015
Messages
145
Hi, you are missing an opening parenthesis here.

Rich (BB code):
=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.
 

SAMCRO2014

Board Regular
Joined
Sep 3, 2015
Messages
145
This is the pay scale table:

Book1
ABCDEFG
1Employee # Start DateEnd DatePay scale groupPS levelWage TypeGross Salary amount
2111112019.04.299999.12.31AU-31100181525
3111112019.04.299999.12.31AU-31100181525
4111112019.04.299999.12.31AU-34100190372
5222222018.09.042019.03.29MG-AFS0591002113721
6222222019.03.302019.07.31MG-AFS0591002113721
7222222019.03.309999.12.31AU-28100191152
8222222019.03.309999.12.31AU-28100191152
9222222019.03.309999.12.31AU-37100199065
10222222019.08.012019.08.30MG-AFS0591002113721
11222222019.08.062019.08.30MG-AFS0591001113721
12222222019.08.319999.12.31MG-AFS0591001113721
13444442018.09.012019.03.29AU-35100293272
14444442018.09.012019.03.29AU-37100299065
15444442018.11.212019.03.29AU-37100199065
16444442019.03.309999.12.31AU-28100191152
17444442019.03.309999.12.31AU-28100191152
18444442019.03.309999.12.31AU-37100199065
19444442019.07.022019.07.12MG-AFS0571002105651
20444442019.08.192019.09.06MG-AFS0571002105651
21555552019.03.162019.03.29AU-471001111682
22555552019.03.182019.03.29AU-571002122276
23555552019.03.182019.03.29AU-571002122276
24555552019.03.302019.12.31AU-571002122276
25555552019.03.302019.12.31AU-571002122276
26555552019.03.309999.12.31AU-471001111682
27555552019.03.309999.12.31AU-471001111682
Pay_Scales


This is the data:
Book1
ABCDEKNO
1Employee # DateOT HoursExpanded OT HoursWage TextAnnual SalaryAnnual Salary Should be
2111112019.05.257.511.25O/T 1st Rest @ 1.5 -Paid90,372
3222222019.06.02510Travel 2ndRest @2.0-Leave99,065
4444442019.05.2569O/T 1st Rest @ 1.5 -Paid99,065
5555552019.05.2723Travel Workday @1.5-Leave111,682
6555552019.05.317.511.25Travel Workday @1.5-Leave111,682
CAS_PLSR
Cell Formulas
Range(s)Formula
K2:K6K2=LOOKUP(B2,Pay_Scales!$B$2:$B$27/(Pay_Scales!$A$2:$A$27=CAS_PLSR!A2),Pay_Scales!$G$2:$G$27)
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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

Board Regular
Joined
Sep 3, 2015
Messages
145
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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 "-".
 

Forum statistics

Threads
1,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

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