Need help with Lookup formula

[Ed Acosta]

Ok I have a Data Entry spreadsheet setup where the user selects the associates name from a drop dwn list. The associate number is automatically entered based on the associate name (simple Vlookup). They then enter the associate Job code and hrs worked in the hrs column.

Associate Associate # Job Code Training Code Hrs Worked
Becky 560 2207 8


Now the tricky part (for me anyway), I have another spreadsheet for associates in training. COL A list associate name, COL B list Associate number, COl C list the job code they are training in, and COL D list the training code.
EXample

Associate Associate # Job Code Training Code
Becky 560 2206 F
Becky 560 2207 J

What I am looking to do is when the Associate is entered in the data entry spreadsheet along with the job code, look in the training spreadsheet and see if the associate is in training for that code, if yes return the training code from the training spreadsheet to the data entry spreadsheet.

 

[Yogi Anand]

Hi Ed:

Let us say this is your first spreadsheet ...

y040207h1a.xls
	A	B	C	D	E
1	Associate	Associate#	JobCode	TrainingCode	HrsWorked
2	Becky	560	2207	J	8
Sheet1

and this is your second spreadsheet ...

y040207h2a.xls
	A	B	C	D
1	Associate	Associate#	JobCode	TrainingCode
2	Becky	560	2206	F
3	Becky	560	2207	J
Sheet1

The formula in cell D2 of your first spreadsheet is ...

=INDEX([y040207h2a.xls]Sheet1!$A$2:$D$3,MATCH(A2&C2,[y040207h2a.xls]Sheet1!$A$2:$A$3&[y040207h2a.xls]Sheet1!$C$2:$C$3,0),4)

this is an array formula and is to be entered with CTRL+SHIFT+ENTER rather than with just ENTER.

 

[Ed Acosta]

Thank you, works perfect