Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    Hi all...

    i have a fairly simple problem with what i think is a pretty hard solution (at least to me)

    so i have a sales comission report and need to pull agent totals to another excel file.
    Seems pretty straight forward.... Except this report creates a new row per sale... so the totals row always changes in relation to the agents name.....

    say agent 1's name is on C7, and HIS totals (this time) are on L12...

    Then agent 2's name is on C19 and HIS totals are on... L26...

    Then agent 3 made lots of sales and HIS name is on... C33 and his totals are on L65...

    note: the totals are always on the same row where the word totals is. (if that matters)


    how can i lookup the totals for each agent?

    i thought i could use a vlookup and look for the name then offset to the row that contains the word "totals" but... IT always changes in reference to the agents name row....
    and even if i did that.... then what? would i need o to another vlookup from that word "totals" to return the actual ammount??? i am seriously lost, and would appreciate any input or tips offered.

    Thanx in advance.
    Last edited by deletedalien; Jun 14th, 2019 at 04:44 PM.
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    If I understand correctly, you could do something like this:

    C L M N O
    1
    2 Name Totals
    3 Bob 1 Anne 105
    4 2 Bob 6
    5 3 Elaine 14
    6 Totals 6
    7 Elaine 2
    8 3
    9 4
    10 5
    11 Totals 14
    12 Anne 5
    13 10
    14 15
    15 20
    16 25
    17 30
    18 Totals 105
    Sheet2

    Worksheet Formulas
    Cell Formula
    O3 =VLOOKUP("Totals",INDEX(C:C,MATCH(N3,C:C,0)+1):L$1000,10,0)



    You'd have to change the references to match your workbooks of course.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    oh geez.....

    im SO excited to try this out.....
    i just got back from lunch...

    gimme 2 minutes to try this...
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    aww man.... it didn't work....

    did i mess up?

    =VLOOKUP("Totals",INDEX(C:C,MATCH(N3,C:C,0)+1):L$1000,10,0)

    is that
    :L$1000 part correct??

    seems like a pretty lonely cell out there....
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    Quote Originally Posted by Eric W View Post
    If I understand correctly, you could do something like this:

    C L M N O
    1
    2 Name Totals
    3 Bob 1 Anne 105
    4 2 Bob 6
    5 3 Elaine 14
    6 Totals 6
    7 Elaine 2
    8 3
    9 4
    10 5
    11 Totals 14
    12 Anne 5
    13 10
    14 15
    15 20
    16 25
    17 30
    18 Totals 105
    Sheet2

    Worksheet Formulas
    Cell Formula
    O3 =VLOOKUP("Totals",INDEX(C:C,MATCH(N3,C:C,0)+1):L$1000,10,0)



    You'd have to change the references to match your workbooks of course.
    yes that is exactly what i need.

    Except i couldnt make that formula work
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    The L$1000 cell is just a cell that should be below the bottom cell of your data. We could actually look for your bottom row, but that would be extra work. And incidentally, the +1 in the middle of the formula is not really needed either (but it doesn't hurt).

    As to why it doesn't work for you, that's hard to tell without seeing your sheets. When you say "doesn't work", does that mean no answer, wrong answer, error message? I don't know if you tried this on your sheets first, or a test sheet, but it might be easier to try a test sheet first. Open a new workbook, enter the data from my example and see if it works. If it does, try changing the ranges to match your other sheets. I know that changing references to match other workbooks can be quite challenging.

    Good luck, and let me know how it goes!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    Quote Originally Posted by Eric W View Post
    The L$1000 cell is just a cell that should be below the bottom cell of your data. We could actually look for your bottom row, but that would be extra work. And incidentally, the +1 in the middle of the formula is not really needed either (but it doesn't hurt).
    oohhhhhhh

    Quote Originally Posted by Eric W View Post
    As to why it doesn't work for you, that's hard to tell without seeing your sheets. When you say "doesn't work", does that mean no answer, wrong answer, error message?
    duhh hahaha good point... sorry about that, My bad.

    i just get an #N/A


    Quote Originally Posted by Eric W View Post
    I don't know if you tried this on your sheets first, or a test sheet, but it might be easier to try a test sheet first. Open a new workbook, enter the data from my example and see if it works. If it does, try changing the ranges to match your other sheets. I know that changing references to match other workbooks can be quite challenging.

    Good luck, and let me know how it goes!
    i tried it on my current comissions sheet, i tried entering this formula on N9 and used N3 as the reference cell..


    and yes i will try this on a test sheet Good idea,

    i'll get back to you in a bit.
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    I MADE IT WORK!!!!!!!! YAY!!!!

    however now it needs fixing... (also this was my mistake for not explaining this in my original post, i didn't think of this)


    so if i place the word "TOTALS" in column "C" yes it works BUT, on my report the word totals is actualy in column "H"
    Last edited by deletedalien; Jun 14th, 2019 at 07:55 PM.
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    497
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    HOLY JESUS!!!!!!

    i FIXED IT!!!!

    i don't know how it works but... I FIXED IT!!!


    =VLOOKUP("Totals",INDEX(H:H,MATCH(N3,C:C,0)+1):L$1000,5,0)

    So the agent name is column C
    The rod Totals in column H
    The total amount in column L (5 columns from L)
    and now it WORKS!!!!!!

    thank you so much Eric W


    YOU FREACKING ROCK!!!!!!!!
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,103
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Vlookup offset? Index? Match? Choose? i honestly have no idea.......

    Way to go! Looks like you did some nice work too!


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
  •