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

deletedalien

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
oh geez.....

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

gimme 2 minutes to try this...
 
Upvote 0
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....
 
Upvote 0
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 :(
 
Upvote 0
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!
 
Upvote 0
Solution
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.
 
Upvote 0
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:
Upvote 0
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
user-online.png


YOU FREACKING ROCK!!!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top