# Thread: Vlookup offset? Index? Match? Choose? i honestly have no idea....... Thanks:  3 Post #5295210 (1)Post #5294680 (1)Post #5294709 (1) Likes:  3 Post #5294709 (1)Post #5295210 (1)Post #5294680 (1)

1. ## 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.

2. ## 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.

3. ## 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...

4. ## 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....

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

Originally Posted by Eric W
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

6. ## 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!

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

Originally Posted by Eric W
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

Originally Posted by Eric W
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 just get an #N/A

Originally Posted by Eric W
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.

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

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"

9. ## 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!!!!!!!!

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

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