Results 1 to 2 of 2

Thread: I think I need a V Lookup?????
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Name Week1 Week2 Week3 Week4
    Name1 1 2 7 0
    Name 2 3 6 0 0
    Name 3 1 2 3 4
    Name 4 2 4 6 7

    Week 1 Week 2 Week 3 Week 4 Week 5 Week6 Week 7
    Name1 Name 1 Name2 Name3 Name2 Name1
    Name3 Name3 Name3 Name4 Name4 Name4
    Name4

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,275
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: I think I need a V Lookup?????

    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.

    ABCDEFG
    1NameWeek1Week2Week3Week4
    2Name11270
    3Name 23600
    4Name 31234
    5Name 42467
    6
    7
    8Week 1Week 2Week 3Week 4Week 5Week 6Week 7
    91234567
    10Name1Name1Name 2Name 3 Name 2Name1
    11Name 3Name 3Name 3Name 4 Name 4Name 4
    12 Name 4

    Spreadsheet Formulas
    CellFormula
    A10=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,(ROW($A$2:$A$5)-ROW($A$2)+1)/(ISNUMBER(SEARCH(A$9,$B$2:$E$5))),ROWS($A$10:A10))),"")


    Excel tables to the web >> Excel Jeanie HTML 4

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •