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

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
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:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,777
If I understand correctly, you could do something like this:

CLMNO
1
2NameTotals
3Bob1Anne105
42Bob6
53Elaine14
6Totals6
7Elaine2
83
94
105
11Totals14
12Anne5
1310
1415
1520
1625
1730
18Totals105

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
oh geez.....

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

gimme 2 minutes to try this...
 

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
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....
 

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
If I understand correctly, you could do something like this:

CLMNO
1
2NameTotals
3Bob1Anne105
42Bob6
53Elaine14
6Totals6
7Elaine2
83
94
105
11Totals14
12Anne5
1310
1415
1520
1625
1730
18Totals105

<tbody>
</tbody>
Sheet2

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

<tbody>
</tbody>

<tbody>
</tbody>



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 :(
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,777
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!
 

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
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

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


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.
 

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
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:

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
503
Office Version
2013
Platform
Windows
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!!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,579
Messages
5,469,503
Members
406,656
Latest member
Kriscrawford76

This Week's Hot Topics

Top