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

deletedalien

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
If I understand correctly, you could do something like this:

CLMNO
NameTotals
BobAnne
Bob
Elaine
Totals
Elaine
Totals
Anne
Totals

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]105[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]105[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]=VLOOKUP("Totals",INDEX(C:C,MATCH(N3,C:C,0)+1):L$1000,10,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
Eric W said:
If I understand correctly, you could do something like this:

CLMNO
NameTotals
BobAnne
Bob
Elaine
Totals
Elaine
Totals
Anne
Totals

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]105[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]105[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]O3[/TH]
[TD="align: left"]=VLOOKUP("Totals",INDEX(C:C,MATCH(N3,C:C,0)+1):L$1000,10,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

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
Eric W said:
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).
Click to expand...

oohhhhhhh

Eric W said:
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?
Click to expand...

duhh hahaha good point... sorry about that, My bad.

i just get an #N/A


Eric W said:
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!
Click to expand...

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
You must log in or register to reply here.

Similar threads

J
  • Question Question
Index Match Formula to generate a report duplicates? Rosters/Vacancies
Replies
9
Views
848
Jay88
J
M
  • Question Question
Advanced INDEX / VLOOKUP / MATCH (??) With row, column AND binary table
Replies
3
Views
820
Fluff
Fluff
S
  • Question Question
Create Script Button to Add Selected Values to the Next empty Row in the sheet
Replies
1
Views
555
shaunreeder
S
T
  • Question Question
Match Bills of Lading Broken Up into Varying Numbers of Rows - Mini Sheet Attached
Replies
3
Views
572
Rijnsent
Rijnsent
J
  • Question Question
VBA for locking sheet after submission of report with date and time stamp
Replies
1
Views
403
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

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