I think I need a V Lookup?????

[crazybuckeyeguy]

I could use some help with a V Lookup. The first sheet is a list of names in column 1 in columns 2-5 those are weeks in the year based on the second sheet. Said person can select any of the 52 weeks and up to 4 different weeks. I am working a vacation bid where people select which week they want off for the following year. This would be the case for about 400 people. on the second sheet is a master that would have the weeks in columns and I need to transfer the names to said week but only take 1 spot per week. Each week is only allowed so many names tho. I would need an error returned that the week is full and no more spots are available. I am struggling with the Vlookup and not filling every cell with 1 persons name for the week they choose. PLEASE HELP as I am in dire need. If this doesn't make sense please let me know and I will try to explain more.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD]Week4[/TD]
[/TR]
[TR]
[TD]Name1
[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Week 5[/TD]
[TD]Week6[/TD]
[TD]Week 7[/TD]
[/TR]
[TR]
[TD]Name1[/TD]
[TD]Name 1[/TD]
[TD]Name2[/TD]
[TD]Name3
[/TD]
[TD][/TD]
[TD]Name2[/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Name3[/TD]
[TD]Name3[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD][/TD]
[TD]Name4[/TD]
[TD]Name4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

See if this does what you want.
You will Excel 2010 or later to use the AGGREGATE function. If you have an earlier ver. of Excel let me know and we can use a different formula.
Change ranges to match your data and copy across and down as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Week1	Week2	Week3	Week4
2	Name1	1	2	7	0
3	Name 2	3	6	0	0
4	Name 3	1	2	3	4
5	Name 4	2	4	6	7
6
7
8	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7
9	1	2	3	4	5	6	7
10	Name1	Name1	Name 2	Name 3		Name 2	Name1
11	Name 3	Name 3	Name 3	Name 4		Name 4	Name 4
12		Name 4
Sheet