How to use two VLOOKUPs in one cell??

[ericaindallas]

Example...
I have a LIST of employees with the dates they were sick, on vacation or had jury duty... I would like to display the information in the OUTPUT FORMAT below with the Description populated by employee, by date.

I have over 30,000 accounts I need to do this with 100 dates.

LIST
Employee Date Description
Belford, Eugene 2/17/09 Sick
Goldstein, Emmanuel 2/26/09 Vacation
Goldstein, Emmanuel 2/27/09 Vacation
Libby, Kate 2/12/09 Vacation
Libby, Kate 2/13/09 Vacation
Libby, Kate 2/16/09 Vacation
Libby, Kate 2/25/09 Sick
Libby, Kate 2/26/09 Sick
Libby, Kate 2/27/09 Sick
Murphy, Dade 2/12/09 Vacation
Murphy, Dade 2/13/09 Vacation
Murphy, Dade 2/16/09 Vacation
Murphy, Dade 2/27/09 Sick
Pardella, Joey 2/9/09 Jury Duty
Sanchez, Ramon 2/9/09 Sick
Sanchez, Ramon 2/10/09 Jury Duty
Sanchez, Ramon 2/11/09 Jury Duty
Sanchez, Ramon 2/11/09 Vacation
Sanchez, Ramon 2/12/09 Vacation

OUTPUT FORMAT
Date
Employee 2/9/09 2/10/09 2/11/09 2/12/09 2/13/09 2/16/09 2/17/09 2/18/09 2/19/09 2/20/09 2/23/09 2/24/09 2/25/09 2/26/09 2/27/09
Belford, Eugene
Goldstein, Emmanuel
Libby, Kate
Murphy, Dade
Pardella, Joey
Sanchez, Ramon


Thanks! Erica

 

[sanrv1f]

Hello and welcome to the board,

Your output format is not very clear,

did you try Pivot table?

If pivot doesn't solve the issue, post output format with expected results

 

[ericaindallas]

Sorry if I was not clear enough... would help if I use cell numbers. I am not using a pivot table because of the large amount of data... the data I enclosed is just an example.

Looking at the OUTPUT FORMAT, let's begin with the first name, "Belford, Eugene" (A3) and the first date "2/9/09" (B2), I would want the formula after the name and under the date (B3, C3, D3, etc.)... for "Belford, Eugene" and the fields would be blank except under "2/17/09" (H3), it would read "Sick"... so on and forth for each person and each date populated with a description or blank.

Thanks! Erica

 

[Anthony47]

I understood that you wish to write at the intersect Employee/Date the type of registered activity.
If this is the case:
-let's suppose your LIST is on Sheet1, colums A-B-C
-you create on Sheet2, from A2 downward, the unique list of employees, and from B1 rightward the list of dates of your concern
Then in B2 you set the formula

=IF(ISERR(VLOOKUP(B$1,OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,0,COUNTIF(Sheet1!$A:$A,$A2),2),2,0)),"",VLOOKUP(B$1,OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,0,COUNTIF(Sheet1!$A:$A,$A2),2),2,0))

Copy this formula to the right, for all the dates you set in line 1, then copy formulas in line 2 down for all the names listed in col A

Bye.

 

[sanrv1f]

try this,

Excel Workbook
	A	B	C
1	Employee	Date	Description
2	Belford, Eugene	17-Feb	Sick
3	Goldstein, Emmanuel	26-Feb	Vacation
4	Goldstein, Emmanuel	27-Feb	Vacation
5	Libby, Kate	12-Feb	Vacation
6	Libby, Kate	13-Feb	Vacation
7	Libby, Kate	16-Feb	Vacation
8	Libby, Kate	25-Feb	Sick
9	Libby, Kate	26-Feb	Sick
10	Libby, Kate	27-Feb	Sick
11	Murphy, Dade	12-Feb	Vacation
12	Murphy, Dade	13-Feb	Vacation
13	Murphy, Dade	16-Feb	Vacation
14	Murphy, Dade	27-Feb	Sick
15	Pardella, Joey	09-Feb	Jury
16	Sanchez, Ramon	09-Feb	Sick
17	Sanchez, Ramon	10-Feb	Jury
18	Sanchez, Ramon	11-Feb	Jury
19	Sanchez, Ramon	11-Feb	Vacation
20	Sanchez, Ramon	12-Feb	Vacation
Sheet2

Excel 2003

Excel Workbook
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		09-Feb	10-Feb	11-Feb	12-Feb	13-Feb	14-Feb	15-Feb	16-Feb	17-Feb	18-Feb	19-Feb	20-Feb	21-Feb
2	Belford, Eugene									Sick
3	Goldstein, Emmanuel
4	Libby, Kate				Vacation	Vacation			Vacation
5	Murphy, Dade				Vacation	Vacation			Vacation
6	Pardella, Joey	Jury
7	Sanchez, Ramon	Sick	Jury	Jury	Vacation
Sheet2

Excel 2003

Cell Formulas
Range	Formula
G2	=IF(SUM(INDEX(($A$2:$A$20=$F2)*($B$2:$B$20=G$1),0)),INDEX($C$2:$C$20,MATCH(1,INDEX(($A$2:$A$20=$F2)*($B$2:$B$20=G$1),0),0)),"")

 

[ericaindallas]

Thank you very much for your help! Worked great

Thanks! Erica