Return value from table based on date and amount

[Aubrey Reel]

How do I return the Position from Table 2 to Column H in Table 1 using the below criteria:

date_week_ended year from Table 1 match to the corresponding column in Table 2
AND
Hourly Rate from Table 1 that is within the range of pay listed in Table 2 (e.g. for Year 2016 Tech 1 range = $11-13.99, Tech 2 range = $14-17.99, etc)

For example:
date_week_ended = 3/31/2016
Hourly Rate = $13.50
Position = Tech 1

Any help appreciated - I am running myself in circles on this one!

 

[etaf]

a grid lookup should work - if the years in table 2 are text

But where is the position in table 1 to lookup ?

 

[etaf]

for example here

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	emp no	date weeke ending				hourly rate
2	1	2/1/16				10
3	2	3/1/18				#N/A
4	3	5/1/23				224
5
6
7
8
9
10
11		2016	2017	2018	2019	2020	2021	2022	2023	2024
12	1	10	12	14	16	18	20	22	24	26
13	3	210	212	214	216	218	220	222	224	226
14
15
16
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($B$12:$J$17,MATCH(A2,$A$12:$A$17,0),MATCH(YEAR(B2),B$11:J$11,0))
B13:J13	B13	=B12+200

 

[Aubrey Reel]

I was thinking some sort of index/match too but couldn't get anything to work. The Position (.e.g Tech 1, Tech 2) is what I am trying to pull into Table 1. Table two contains the starting rate for each position by year. Each employees position is based on their pay - so an employee who starts as a Tech 1 becomes a Tech 2 when they reach the $14 pay rate, and then an Operator when they reach $18, etc. To pull the Position into Table 1, I need to look up the year they worked (based on date_week_ended column) and how much they were being paid per hour (Hourly Rate column) to determine what their Position was at that time. I need this information in order to calculate average hourly rate by position for each historical quarter. Below are the images with the column and row numbers visible.

Table 1

Table 2

 

[etaf]

ok, so not 100% following

you want to look down the column based on the year
so the green highlight is
3/31/2016 - column B - Table 1
so look down the 2016 column and find the Hourly rate - Table 2 column B
which is 13.50 Column F table 1

BUT 13.50 does not exist in table 2 column B 2016
so now what do you want to do

if it had existed , then you want to return the Position from table 2 column A

 

[Aubrey Reel]

The $13.50 would be in the range $13-14.99 for a Tech 1 in year 2016. So the formula would have to somehow reference greater than or equal to $13.50 and less than $15.00, which is the starting rate of the next Position level of Tech 2 in 2016, and return Tech 1. Each year, the ranges for each position would be different, although the data currently is just a plug for illustration.

 

[etaf]

thanks for that
not sure i know how to do that , I have examples of how i can get the horizontal header from an exact number in a grid, not sure on the greater than and row label

just to clarify further

as its 13.50 - then the next greater is !5 , which is Tech2

somehow reference greater than or equal to $13.50 and less than $15.00, which is the starting rate of the next Position level of Tech 2 in 2016, and return Tech 1.

Which means not the greater value , but the lower value.. as you said

range $13-14.99 for a Tech 1

I'll continue to have a look
BUT also
You show as having version 2010
is that the version you are using , as a solution may be very different if you have later versions

 

[Aubrey Reel]

Thank you for trying to help. I believe I am still running version 2010 - below are the screenshots. If it helps, Table 2 can be set up pretty much any way as long as it has the same info.

Account info in Excel:

About info on PC:

 

[Fluff]

I believe I am still running version 2010

The image shows that you are running 365, so I suggest that you update your profile.

 

[awoohaw]

I have changed table 2 so that i/you could see how the formulas progress. But, try this:

This is using date values in all cells that are date appropriate. even the headers in table 2, which is why you see the YEAR function in the formulas. Also, this should work with nearly all versions of excel, at least as far back as 2010.

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Table1
2	Employee_no	date_week_ended	regular_hours	regular		hourly_rate	Position
3	1379	2016-03-31				13.5	Tech 1
4	1378	2017-03-31				14.5	Tech 1
5	458	2019-03-31				18	Tech 2
6	1311	2023-03-31				25	Operator
7
8	Table2
9	Starting Rates
10	Position	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025
11	Tech 1	13	13.75	14.5	15.3	16	16.75	17.5	18.25	19	19.8
12	Tech 2	15	15.75	16.5	17.3	18	18.75	19.5	20.25	21	21.8
13	Operator	18	18.75	19.5	20.3	21	21.75	22.5	23.25	24	24.8
14	Foreman	20	20.75	21.5	22.3	23	23.75	24.5	25.25	26	26.8
aubrey reel

Cell Formulas
Range	Formula
G3:G6	G3	=INDEX($A$11:$A$14,MATCH($F3,INDEX($B$11:$K$14,1,MATCH(YEAR(B3),YEAR($B$10:$K$10),0)):INDEX($B$11:$K$14,ROWS($B$11:$B$14),MATCH(YEAR(B3),YEAR($B$10:$K$10),0)),1))