Results 1 to 4 of 4

Thread: Vlookup - multiple columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup - multiple columns

    Hi,

    Every month a sales report is pulled showing all activity for each sales person from the beginning of the year. This tab is the total sales overview. It is sorted alphabetical per sales person.

    Then a tab is created per sales person.

    Each of these sales person tabs needs to reflect all data related to this person.

    I have tried vlookup and column(a1)+1 combo formula to extract all columns. Although the formula works, it creates an issue. For example Sales person Adam is on line 50. When applying the formula, it will duplicate the first line of this sales person 50 times and then it start extracting correcting. It can be easily fixed by using the "delete duplicates" but it is cumbersome.

    Ideal would be:
    1. have a tab per sales person with appropriate headers and in a cell outside those headers with his/her name as basis for lookup cell. Right now I am just copying the name in the first column.
    2. lookup that name in the total sales overview tab and just extract all the info for this sales person from column A thru D (including the Name of the person)
    3. Preferable an "iferror" then show " " formula would be ideal as the formula could then be copies till for example row 100 and would automatically filled each month. The file is pulled each month. Say every month each sales person makes 5 sales then January for example will only 5 lines of data show, in February it will show 10 line etc. and finally in December it will show 60 lines.
    4. Pivot table and macro are not desirable
    5. Finally: save this document as values only (could this be possible by the function "do not show formulas" for the whole spreadsheet?). I want to keep the formula based spreadsheet as my working tool - the spreadsheet with just the values is for the appropriate department

    Below are my attempts:
    either
    =VLOOKUP(A17,$A$1:$D$13,{2,3,4},FALSE)
    either
    =VLOOKUP(G18,G3:J14,COLUMNS(G3)+1,FALSE)

    Any help to put me in the right direction would be greatly appreciated.

    Thank you!

    Sheet1

    A B C D E F G H I J
    1 Name Date Amount Region Name Date Amount Region
    2 John 01/01/19 200 North John 01/01/19 200 North
    3 John 02/02/19 500 North John 02/02/19 500 North
    4 John 03/03/19 600 North John 03/03/19 600 North
    5 John 04/05/19 100 North John 04/05/19 100 North
    6 Ed 01/01/19 200 South Ed 01/01/19 200 South
    7 Ed 02/02/19 500 South Ed 02/02/19 500 South
    8 Ed 03/03/19 150 South Ed 03/03/19 150 South
    9 Ed 04/05/19 250 South Ed 04/05/19 250 South
    10 Adam 01/01/19 350 West Adam 01/01/19 350 West
    11 Adam 02/02/19 170 West Adam 02/02/19 170 West
    12 Adam 03/03/19 600 West Adam 03/03/19 600 West
    13 Adam 04/05/19 800 West Adam 04/05/19 800 West
    14
    15
    16 Name Date Amount Region Name Date Amount Region
    17 John 01/01/19 200 North John 01/01/19 200 North
    18 John 01/01/19 200 North John 02/02/19 500 North
    19 John 01/01/19 200 South John 03/03/19 600 North
    20 John 01/01/19 200 South John 04/05/19 100 North
    21
    22
    23 Ed 02/02/19 500 South Ed 03/03/19 150 South
    24 Ed 03/03/19 150 South Ed 04/05/19 250 South
    25 Ed 04/05/19 250 South Ed #N/A #N/A #N/A
    26 Ed #N/A #N/A #N/A Ed #N/A #N/A #N/A
    27
    28
    29 Adam 01/01/19 350 West Adam #N/A #N/A #N/A
    30 Adam 01/01/19 350 West Adam #N/A #N/A #N/A
    31 Adam 01/01/19 350 West Adam #N/A #N/A #N/A
    32 Adam 01/01/19 350 West Adam #N/A #N/A #N/A

    Spreadsheet Formulas
    Cell Formula
    B17 =VLOOKUP(A17,$A$1:$D$13,{2,3,4},FALSE)
    C17 =VLOOKUP(B17,B1:E13,{2,3,4},FALSE)
    D17 =VLOOKUP(C17,C1:F13,{2,3,4},FALSE)
    H17 =VLOOKUP(G17,G2:J13,COLUMNS(G2)+1,FALSE)
    I17 =VLOOKUP(H17,H1:K13,COLUMNS(H2)+1,FALSE)
    J17 =VLOOKUP(I17,I1:L13,COLUMNS(I2)+1,FALSE)
    B18 =VLOOKUP(A18,$A$1:$D$13,{2,3,4},FALSE)
    C18 =VLOOKUP(B18,B2:E14,{2,3,4},FALSE)
    D18 =VLOOKUP(C18,C2:F14,{2,3,4},FALSE)
    H18 =VLOOKUP(G18,G3:J14,COLUMNS(G3)+1,FALSE)
    I18 =VLOOKUP(H18,H2:K14,COLUMNS(H3)+1,FALSE)
    J18 =VLOOKUP(I18,I2:L14,COLUMNS(I3)+1,FALSE)
    B19 =VLOOKUP(A19,$A$1:$D$13,{2,3,4},FALSE)
    C19 =VLOOKUP(B19,B3:E15,{2,3,4},FALSE)
    D19 =VLOOKUP(C19,C3:F15,{2,3,4},FALSE)
    H19 =VLOOKUP(G19,G4:J15,COLUMNS(G4)+1,FALSE)
    I19 =VLOOKUP(H19,H3:K15,COLUMNS(H4)+1,FALSE)
    J19 =VLOOKUP(I19,I3:L15,COLUMNS(I4)+1,FALSE)
    B20 =VLOOKUP(A20,$A$1:$D$13,{2,3,4},FALSE)
    C20 =VLOOKUP(B20,B4:E16,{2,3,4},FALSE)
    D20 =VLOOKUP(C20,C4:F16,{2,3,4},FALSE)
    H20 =VLOOKUP(G20,G5:J16,COLUMNS(G5)+1,FALSE)
    I20 =VLOOKUP(H20,H4:K16,COLUMNS(H5)+1,FALSE)
    J20 =VLOOKUP(I20,I4:L16,COLUMNS(I5)+1,FALSE)
    B23 =VLOOKUP(A23,A7:D19,{2,3,4},FALSE)
    C23 =VLOOKUP(B23,B7:E19,{2,3,4},FALSE)
    D23 =VLOOKUP(C23,C7:F19,{2,3,4},FALSE)
    H23 =VLOOKUP(G23,G8:J19,COLUMNS(G8)+1,FALSE)
    I23 =VLOOKUP(H23,H7:K19,COLUMNS(H8)+1,FALSE)
    J23 =VLOOKUP(I23,I7:L19,COLUMNS(I8)+1,FALSE)
    B24 =VLOOKUP(A24,A8:D20,{2,3,4},FALSE)
    C24 =VLOOKUP(B24,B8:E20,{2,3,4},FALSE)
    D24 =VLOOKUP(C24,C8:F20,{2,3,4},FALSE)
    H24 =VLOOKUP(G24,G9:J20,COLUMNS(G9)+1,FALSE)
    I24 =VLOOKUP(H24,H8:K20,COLUMNS(H9)+1,FALSE)
    J24 =VLOOKUP(I24,I8:L20,COLUMNS(I9)+1,FALSE)
    B25 =VLOOKUP(A25,A9:D21,{2,3,4},FALSE)
    C25 =VLOOKUP(B25,B9:E21,{2,3,4},FALSE)
    D25 =VLOOKUP(C25,C9:F21,{2,3,4},FALSE)
    H25 =VLOOKUP(G25,G10:J21,COLUMNS(G10)+1,FALSE)
    I25 =VLOOKUP(H25,H9:K21,COLUMNS(H10)+1,FALSE)
    J25 =VLOOKUP(I25,I9:L21,COLUMNS(I10)+1,FALSE)
    B26 =VLOOKUP(A26,A10:D22,{2,3,4},FALSE)
    C26 =VLOOKUP(B26,B10:E22,{2,3,4},FALSE)
    D26 =VLOOKUP(C26,C10:F22,{2,3,4},FALSE)
    H26 =VLOOKUP(G26,G11:J22,COLUMNS(G11)+1,FALSE)
    I26 =VLOOKUP(H26,H10:K22,COLUMNS(H11)+1,FALSE)
    J26 =VLOOKUP(I26,I10:L22,COLUMNS(I11)+1,FALSE)
    B29 =VLOOKUP($A29,$A$1:$D$13,COLUMN(A14)+1,0)
    C29 =VLOOKUP($A29,$A$1:$D$13,COLUMN(B14)+1,0)
    D29 =VLOOKUP($A29,$A$1:$D$13,COLUMN(C14)+1,0)
    H29 =VLOOKUP(G29,G14:J25,COLUMNS(G14)+1,FALSE)
    I29 =VLOOKUP(H29,H13:K25,COLUMNS(H14)+1,FALSE)
    J29 =VLOOKUP(I29,I13:L25,COLUMNS(I14)+1,FALSE)
    B30 =VLOOKUP($A30,$A$1:$D$13,COLUMN(A15)+1,0)
    C30 =VLOOKUP($A30,$A$1:$D$13,COLUMN(B15)+1,0)
    D30 =VLOOKUP($A30,$A$1:$D$13,COLUMN(C15)+1,0)
    H30 =VLOOKUP(G30,G15:J26,COLUMNS(G15)+1,FALSE)
    I30 =VLOOKUP(H30,H14:K26,COLUMNS(H15)+1,FALSE)
    J30 =VLOOKUP(I30,I14:L26,COLUMNS(I15)+1,FALSE)
    B31 =VLOOKUP($A31,$A$1:$D$13,COLUMN(A16)+1,0)
    C31 =VLOOKUP($A31,$A$1:$D$13,COLUMN(B16)+1,0)
    D31 =VLOOKUP($A31,$A$1:$D$13,COLUMN(C16)+1,0)
    H31 =VLOOKUP(G31,G16:J27,COLUMNS(G16)+1,FALSE)
    I31 =VLOOKUP(H31,H15:K27,COLUMNS(H16)+1,FALSE)
    J31 =VLOOKUP(I31,I15:L27,COLUMNS(I16)+1,FALSE)
    B32 =VLOOKUP($A32,$A$1:$D$13,COLUMN(A17)+1,0)
    C32 =VLOOKUP($A32,$A$1:$D$13,COLUMN(B17)+1,0)
    D32 =VLOOKUP($A32,$A$1:$D$13,COLUMN(C17)+1,0)
    H32 =VLOOKUP(G32,G17:J28,COLUMNS(G17)+1,FALSE)
    I32 =VLOOKUP(H32,H16:K28,COLUMNS(H17)+1,FALSE)
    J32 =VLOOKUP(I32,I16:L28,COLUMNS(I17)+1,FALSE)


    Excel tables to the web >> Excel Jeanie HTML 4

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,019
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Vlookup - multiple columns

    If I have understood correctly, then you might be able to adapt this:
    https://www.mrexcel.com/forum/excel-...ml#post2122886
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - multiple columns

    Hi Peter, Thank you very much for your reply. I tried your approach at my work spreadsheet and it did not work. I started a new spreadsheet and am trying to work with below formula. It does copy all cells from the original table but I can't figure out how to lock in just one name which is in cell L1 ...
    would you have a suggestion?
    Thank you
    =INDEX($A$2:$D$24,IF(COUNTIF($L$1:$L$1,$A$2:$D$24),MATCH(ROW($A$2:$D$24),ROW($A$2:$D$24),""),ROWS($G2:G$2)),COLUMNS($A$2:A2))

    Name date Amount Location Name date Amount Location john
    john 01/01/19 100 North john 01/01/19 100 North
    john 02/01/19 200 North john 02/01/19 200 North
    john 03/01/19 500 North john 03/01/19 500 North
    sam 01/02/19 50 South sam 01/02/19 50 South
    sam 02/02/19 150 South sam 02/02/19 150 South
    sam 03/02/19 200 South sam 03/02/19 200 South
    sam 04/02/19 220 South sam 04/02/19 220 South
    ken 01/03/19 70 West ken 01/03/19 70 West
    ken 02/03/19 100 West ken 02/03/19 100 West
    ken 03/03/19 120 West ken 03/03/19 120 West
    ken 03/04/19 210 West ken 03/04/19 210 West
    ken 04/05/19 220 West ken 04/05/19 220 West
    ron 01/04/19 50 NE ron 01/04/19 50 NE
    ron 01/16/19 110 NE ron
    an 02/01/19 200 East
    an 03/05/19 350 East
    an 04/07/19 370 East
    an 04/10/19 410 East
    an 05/16/19 450 East
    lena 01/07/19 100 SW
    lena 02/09/19 175 SW
    lena 03/07/19 225 SW
    lena 05/01/19 350 SW
    Last edited by Vcoppens; Aug 3rd, 2019 at 02:19 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2012
    Posts
    68
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup - multiple columns

    Hi Peter, I just worked a little longer with your suggested formula and it did work.
    Question: is there a way to automatic fill the first row instead of typing manually the name of the person?
    Thank you very much!
    Name date Amount Location
    john 01/01/19 100 North 2
    02/01/19 200 North 3
    03/01/19 500 North 4
    #N/A #N/A #N/A #N/A

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
  •