lookup two variables question

[lillian]

Hi all,

I've restructured a data table I've been struggling with & now need to populate the original table from the newly formatted data. I've tried to use Index/Match and If/And formulas, but the results aren't good.

This is my first attempt to post with HTML maker, so... here goes:

Goal: check first name on both sheets (col a on monthly sheet, col e on data re-format), date on both sheets (row 3 on monthly sheet, col b on data re-format) & return start time/total hours (col I on data re-format) data to the monthly sheet, for each day in the month(E4:AI65 on the monthly sheet):

Monthly data sheet:

htmlmakersheets.xls
	A	B	C	D	E	F	G	H
1					Tue	3	3	3
2				jan	Sun	Mon	Tue	Wed
3	First name	Last name	Assignment	Rotation	01	02	03	04
4	Terese A.	xxxxxx	Rotation	#N/A	00:00\38719	#N/A	#N/A	#N/A
5	Melinda A.	xxxxxx	Rotation	#N/A	00:00\38718	#N/A	#N/A	#N/A
6	Sharol B.	xxxxxx	charge		00:00\38719	#N/A	#N/A	#N/A
7	Meg B.	xxxxxx	Rotation	#N/A	00:00\38719	#N/A	#N/A	#N/A
8	Anne C.	xxxxxx	Rotation	#N/A	00:00\38721	#N/A	#N/A	#N/A
9	Susan C.	xxxxxx	Rotation	#N/A	00:00\38719	#N/A	#N/A	#N/A
Jan monthly

more soon.

 

[lillian]

trying again

maybe one screencap per post??

Data re-formatted:

htmlmakersheets.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Date	DoW	S. Rot	Name	SurName	Assign	Rot	start&tot.hrs	Start	Tot.hrs	End
2	Jan	01	Sun	3	Melinda A.	XXXXX	Rotation	B	08:30\8	8:30	8	17:00
3	Jan	01	Sun	3	Cheri C.	XXXXX	R		07:30\10	7:30	10	18:00
4	Jan	01	Sun	3	Eileen C.	XXXXX	R		07:30\10	7:30	10	18:00
5	Jan	01	Sun	3	Lisa E.	XXXXX	charge	LEAD	06:30\10	6:30	10	17:00
6	Jan	01	Sun	3	Janine J.	XXXXX	Rotation	B	08:30\9	8:30	9	18:00
7	Jan	01	Sun	3	Zebib S.	XXXXX	R		07:30\10	7:30	10	18:00
data

 

[lillian]

Lookup name & date, return start time/total hours

Hi all,

I think I can better summarize my ask. In addition to the various formulas contained in the screencaps above, I need to be able to match the first name & date and return the start time/total hours.

When I try formulas that return text, I get an error #Value & when I try formulas that return values I get an error #Num. I'm stumped, but will try to rally!

Any input is welcome.

 

[barry houdini]

In E4 copied down and across

=INDEX(data!$I$2:$I$100,MATCH(1,INDEX((data!$B$2:$B$100=E$3)*(data!$E$2:$E$100=$A4),0),0))

adjust ranges as necessary

 

[lillian]

Ta Barry!

Thanks for the formula, it's working perfectly & I'm tweaking for range.

Thanks again!

edited to update message.