Search data multiple fields & enter data from other cell

[marsham]

I've been trying to work on these formulae in stages & have confused myself so much & now, back to square one & is a massive spreadsheet.

I have a worksheet named Data. This comprises a list of names, start & finish dates & values - some names have different values between certain dates.
On worksheet 2, named Review.
I need to search the Datasheet for the correct name, then search to find which date line it falls within & the result would be the value.

I have attached a dummy of the spreadsheet
Columns in orange are the relevant data
Columns in green are the expected results (when I do it by eye)

 

[kweaver]

You have a better chance of getting forum help if you use XL2BB to show your sample data. Otherwise, it's very difficult to reconstruct your data and sheets.
Also, please change your profile to indicate the version of Excel you are running since there are different options possible with different versions.

One observation from your images: what happens when an employee is duplicated (e.g., Spec Adam A) ?

 

[marsham]

Rate Search - Check.xlsx
	N
16
Review

Rate Search - Check.xlsx
	Q
3
Data

 

[kweaver]

Nice try, but nothing was shown in those two.

 

[marsham]

Nice try, but nothing was shown in those two.

Sorry - try again

Running Excel 365

With any duplicate name, dates - if it throws an error, it will let me go in & clean up the data sheet which has info which my be incorrect

Rate Search - Check.xlsx
	A	B	C	D	E	F	G	H		J	K
1										1/07/2021	31/06/22
2				Charged		Corrections
3	Category	Resource		Cost Rate	Charge Rate	Reference	CONC	Rate		Start Date	End Date
4	Staff	Adam A	STD	$1.00	$2.00	1.4	1.4 STD	$5.00		17/05/2021	20/05/2022
5	Staff	Adam A - NWR	NWR	$5.00	$10.00	1.4	1.4 NWR	$15.00		17/05/2021	20/05/2022
6	Staff	Spec Adam A	STD	$2.00	$3.00	1.4	1.4 STD	$5.00		17/05/2021	20/05/2022
7	Staff	Spec Adam A - NWR	NWR	$7.50	$15.00	1.4	1.4 NWR	$15.00		17/05/2021	20/05/2022
8	Staff	Spec Adam A	STD	$4.00	$5.00	1.4	1.4 STD	$5.00		17/05/2021	20/05/2022
9	Crew	Spec Eve E	STD	$5.00	$6.00	1.3	1.3 STD	$10.00		1/07/2019	27/06/2021
10	Crew	Spec Eve E	STD	$6.00	$7.00	1.3	1.3 STD	$10.00		28/06/2021	30/04/2022
11	Crew	Spec Eve E	STD	$7.00	$8.00	1.3	1.3 STD	$10.00		1/05/2022	2/05/2023
12	Crew	Spec Eve E - NWR	NWR	$10.00	$20.00	1.3	1.3 NWR	$20.00		1/07/2019	27/06/2021
13	Crew	Spec Eve E - NWR	NWR	$12.00	$24.00	1.3	1.3 NWR	$20.00		28/06/2021	30/04/2022
14	Crew	Spec Eve E - NWR	NWR	$15.00	$30.00	1.3	1.3 NWR	$20.00		1/05/2022	2/05/2023
15	Crew	Spec Eve E - SBR	SBR	$2.50	$3.00	1.3	1.3 SBR	$10.00		23/08/2021	30/04/2022
16	Crew	Spec Eve E - SBR	SBR	$3.50	$4.00	1.3	1.3 SBR	$10.00		1/05/2022	2/05/2023
Data

Cell Formulas
Range	Formula
K11,K16,K14	K11	=NOW()
G9:G16	G9	=CONCATENATE(F9," ",C9)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4:K6	Expression	=AND($J4>=$J$1, $J4<=$K$1)	text	NO
K1:K6,K18:K1048360	Cell Value	contains "NOW()"	text	NO

Rate Search - Check.xlsx A B C D E F G H I J K L M N O P Q R 1 Job Emp Type Job Start End Units Chg Cost B na COST USED CHG USED CORRECT RATE COST USED CHG USED CORRECT RATE 2 61071 Adam A Staff 01-Dec-2021 18:30 06:30 12 $120.00 $90.00 75589 $1.00 $2.00 $5.00 3 61086 Adam A - NWR Staff 02-Dec-2021 19:00 05:00 10 $100.00 $75.00 75623 $5.00 $10.00 $15.00 4 61155 Spec Adam A Staff 09-Dec-2021 19:00 04:00 8.5 $85.00 $63.75 75759 $2.00 $3.00 $5.00 5 60884 Spec Eve E Crew 17-Nov-2021 10:00 11:30 1.5 $15.00 $11.25 75126 $6.00 $7.00 $15.00 6 61071 Spec Eve E - NWR Crew 01-Dec-2021 18:45 06:00 11.25 $168.75 $337.50 75589 $12.00 $24.00 $5.00 7 61086 Spec Eve E - NWR Crew 02-Dec-2021 18:30 04:45 10.25 $153.75 $307.50 75591 $12.00 $24.00 $10.00 8 61123 Spec Eve E - SBR Crew 06-Dec-2021 18:45 05:15 10 $150.00 $300.00 75682 $2.50 $3.00 $10.00 9 61124 Spec Eve E - SBR Crew 07-Dec-2021 18:45 05:30 10.75 $161.25 $80.63 75705 $2.50 $3.00 $10.00 10 11 EXPECTED RESULT Formulated Result Review Cell Formulas Range Formula L2:M4 L2 =Data!D4 N2:N9 N2 =Data!H4 L5:M5 L5 =Data!D10 L6:M6,L8:M8 L6 =Data!D13 L7:M7,L9:M9 L7 =Data!D13 I6:I8 I6 =H6*2 I9 I9 =H9/2

 

[kweaver]

You got the XL2BB correct this time.
My first observation, which has nothing to do with your request, is that placing the date in ALL of the rows is going to drastically blow up your file size and slow down your application.

My second observation and question I posed initially is what do you want done with an entry such as "Spec Adam A" when this person's dates are the same in both entries on the Data sheet?

After a bit more coffee, I'll take a look at your issue.