Vlookup - multiple columns

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
73
Hi,

Every month a sales report is pulled showing all activity for each sales person from the beginning of the year. This tab is the total sales overview. It is sorted alphabetical per sales person.

Then a tab is created per sales person.

Each of these sales person tabs needs to reflect all data related to this person.

I have tried vlookup and column(a1)+1 combo formula to extract all columns. Although the formula works, it creates an issue. For example Sales person Adam is on line 50. When applying the formula, it will duplicate the first line of this sales person 50 times and then it start extracting correcting. It can be easily fixed by using the "delete duplicates" but it is cumbersome.

Ideal would be:
1. have a tab per sales person with appropriate headers and in a cell outside those headers with his/her name as basis for lookup cell. Right now I am just copying the name in the first column.
2. lookup that name in the total sales overview tab and just extract all the info for this sales person from column A thru D (including the Name of the person)
3. Preferable an "iferror" then show " " formula would be ideal as the formula could then be copies till for example row 100 and would automatically filled each month. The file is pulled each month. Say every month each sales person makes 5 sales then January for example will only 5 lines of data show, in February it will show 10 line etc. and finally in December it will show 60 lines.
4. Pivot table and macro are not desirable
5. Finally: save this document as values only (could this be possible by the function "do not show formulas" for the whole spreadsheet?). I want to keep the formula based spreadsheet as my working tool - the spreadsheet with just the values is for the appropriate department

Below are my attempts:
either
=VLOOKUP(A17,$A$1:$D$13,{2,3,4},FALSE)
either
=VLOOKUP(G18,G3:J14,COLUMNS(G3)+1,FALSE)

Any help to put me in the right direction would be greatly appreciated.

Thank you!

Sheet1

ABCDEFGHIJ
1NameDateAmountRegion NameDateAmountRegion
2John01/01/19200North John01/01/19200North
3John02/02/19500North John02/02/19500North
4John03/03/19600North John03/03/19600North
5John04/05/19100North John04/05/19100North
6Ed01/01/19200South Ed01/01/19200South
7Ed02/02/19500South Ed02/02/19500South
8Ed03/03/19150South Ed03/03/19150South
9Ed04/05/19250South Ed04/05/19250South
10Adam01/01/19350West Adam01/01/19350West
11Adam02/02/19170West Adam02/02/19170West
12Adam03/03/19600West Adam03/03/19600West
13Adam04/05/19800West Adam04/05/19800West
14
15
16NameDateAmountRegion NameDateAmountRegion
17John01/01/19200North John01/01/19200North
18John01/01/19200North John02/02/19500North
19John01/01/19200South John03/03/19600North
20John01/01/19200South John04/05/19100North
21
22
23Ed02/02/19500South Ed03/03/19150South
24Ed03/03/19150South Ed04/05/19250South
25Ed04/05/19250South Ed#N/A#N/A#N/A
26Ed#N/A#N/A#N/A Ed#N/A#N/A#N/A
27
28
29Adam01/01/19350West Adam#N/A#N/A#N/A
30Adam01/01/19350West Adam#N/A#N/A#N/A
31Adam01/01/19350West Adam#N/A#N/A#N/A
32Adam01/01/19350West Adam#N/A#N/A#N/A

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B17=VLOOKUP(A17,$A$1:$D$13,{2,3,4},FALSE)
C17=VLOOKUP(B17,B1:E13,{2,3,4},FALSE)
D17=VLOOKUP(C17,C1:F13,{2,3,4},FALSE)
H17=VLOOKUP(G17,G2:J13,COLUMNS(G2)+1,FALSE)
I17=VLOOKUP(H17,H1:K13,COLUMNS(H2)+1,FALSE)
J17=VLOOKUP(I17,I1:L13,COLUMNS(I2)+1,FALSE)
B18=VLOOKUP(A18,$A$1:$D$13,{2,3,4},FALSE)
C18=VLOOKUP(B18,B2:E14,{2,3,4},FALSE)
D18=VLOOKUP(C18,C2:F14,{2,3,4},FALSE)
H18=VLOOKUP(G18,G3:J14,COLUMNS(G3)+1,FALSE)
I18=VLOOKUP(H18,H2:K14,COLUMNS(H3)+1,FALSE)
J18=VLOOKUP(I18,I2:L14,COLUMNS(I3)+1,FALSE)
B19=VLOOKUP(A19,$A$1:$D$13,{2,3,4},FALSE)
C19=VLOOKUP(B19,B3:E15,{2,3,4},FALSE)
D19=VLOOKUP(C19,C3:F15,{2,3,4},FALSE)
H19=VLOOKUP(G19,G4:J15,COLUMNS(G4)+1,FALSE)
I19=VLOOKUP(H19,H3:K15,COLUMNS(H4)+1,FALSE)
J19=VLOOKUP(I19,I3:L15,COLUMNS(I4)+1,FALSE)
B20=VLOOKUP(A20,$A$1:$D$13,{2,3,4},FALSE)
C20=VLOOKUP(B20,B4:E16,{2,3,4},FALSE)
D20=VLOOKUP(C20,C4:F16,{2,3,4},FALSE)
H20=VLOOKUP(G20,G5:J16,COLUMNS(G5)+1,FALSE)
I20=VLOOKUP(H20,H4:K16,COLUMNS(H5)+1,FALSE)
J20=VLOOKUP(I20,I4:L16,COLUMNS(I5)+1,FALSE)
B23=VLOOKUP(A23,A7:D19,{2,3,4},FALSE)
C23=VLOOKUP(B23,B7:E19,{2,3,4},FALSE)
D23=VLOOKUP(C23,C7:F19,{2,3,4},FALSE)
H23=VLOOKUP(G23,G8:J19,COLUMNS(G8)+1,FALSE)
I23=VLOOKUP(H23,H7:K19,COLUMNS(H8)+1,FALSE)
J23=VLOOKUP(I23,I7:L19,COLUMNS(I8)+1,FALSE)
B24=VLOOKUP(A24,A8:D20,{2,3,4},FALSE)
C24=VLOOKUP(B24,B8:E20,{2,3,4},FALSE)
D24=VLOOKUP(C24,C8:F20,{2,3,4},FALSE)
H24=VLOOKUP(G24,G9:J20,COLUMNS(G9)+1,FALSE)
I24=VLOOKUP(H24,H8:K20,COLUMNS(H9)+1,FALSE)
J24=VLOOKUP(I24,I8:L20,COLUMNS(I9)+1,FALSE)
B25=VLOOKUP(A25,A9:D21,{2,3,4},FALSE)
C25=VLOOKUP(B25,B9:E21,{2,3,4},FALSE)
D25=VLOOKUP(C25,C9:F21,{2,3,4},FALSE)
H25=VLOOKUP(G25,G10:J21,COLUMNS(G10)+1,FALSE)
I25=VLOOKUP(H25,H9:K21,COLUMNS(H10)+1,FALSE)
J25=VLOOKUP(I25,I9:L21,COLUMNS(I10)+1,FALSE)
B26=VLOOKUP(A26,A10:D22,{2,3,4},FALSE)
C26=VLOOKUP(B26,B10:E22,{2,3,4},FALSE)
D26=VLOOKUP(C26,C10:F22,{2,3,4},FALSE)
H26=VLOOKUP(G26,G11:J22,COLUMNS(G11)+1,FALSE)
I26=VLOOKUP(H26,H10:K22,COLUMNS(H11)+1,FALSE)
J26=VLOOKUP(I26,I10:L22,COLUMNS(I11)+1,FALSE)
B29=VLOOKUP($A29,$A$1:$D$13,COLUMN(A14)+1,0)
C29=VLOOKUP($A29,$A$1:$D$13,COLUMN(B14)+1,0)
D29=VLOOKUP($A29,$A$1:$D$13,COLUMN(C14)+1,0)
H29=VLOOKUP(G29,G14:J25,COLUMNS(G14)+1,FALSE)
I29=VLOOKUP(H29,H13:K25,COLUMNS(H14)+1,FALSE)
J29=VLOOKUP(I29,I13:L25,COLUMNS(I14)+1,FALSE)
B30=VLOOKUP($A30,$A$1:$D$13,COLUMN(A15)+1,0)
C30=VLOOKUP($A30,$A$1:$D$13,COLUMN(B15)+1,0)
D30=VLOOKUP($A30,$A$1:$D$13,COLUMN(C15)+1,0)
H30=VLOOKUP(G30,G15:J26,COLUMNS(G15)+1,FALSE)
I30=VLOOKUP(H30,H14:K26,COLUMNS(H15)+1,FALSE)
J30=VLOOKUP(I30,I14:L26,COLUMNS(I15)+1,FALSE)
B31=VLOOKUP($A31,$A$1:$D$13,COLUMN(A16)+1,0)
C31=VLOOKUP($A31,$A$1:$D$13,COLUMN(B16)+1,0)
D31=VLOOKUP($A31,$A$1:$D$13,COLUMN(C16)+1,0)
H31=VLOOKUP(G31,G16:J27,COLUMNS(G16)+1,FALSE)
I31=VLOOKUP(H31,H15:K27,COLUMNS(H16)+1,FALSE)
J31=VLOOKUP(I31,I15:L27,COLUMNS(I16)+1,FALSE)
B32=VLOOKUP($A32,$A$1:$D$13,COLUMN(A17)+1,0)
C32=VLOOKUP($A32,$A$1:$D$13,COLUMN(B17)+1,0)
D32=VLOOKUP($A32,$A$1:$D$13,COLUMN(C17)+1,0)
H32=VLOOKUP(G32,G17:J28,COLUMNS(G17)+1,FALSE)
I32=VLOOKUP(H32,H16:K28,COLUMNS(H17)+1,FALSE)
J32=VLOOKUP(I32,I16:L28,COLUMNS(I17)+1,FALSE)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
73
Hi Peter, Thank you very much for your reply. I tried your approach at my work spreadsheet and it did not work. I started a new spreadsheet and am trying to work with below formula. It does copy all cells from the original table but I can't figure out how to lock in just one name which is in cell L1 ...
would you have a suggestion?
Thank you
=INDEX($A$2:$D$24,IF(COUNTIF($L$1:$L$1,$A$2:$D$24),MATCH(ROW($A$2:$D$24),ROW($A$2:$D$24),""),ROWS($G2:G$2)),COLUMNS($A$2:A2))

NamedateAmountLocationNamedateAmountLocationjohn
john01/01/19100Northjohn01/01/19100North
john02/01/19200Northjohn02/01/19200North
john03/01/19500Northjohn03/01/19500North
sam01/02/1950Southsam01/02/1950South
sam02/02/19150Southsam02/02/19150South
sam03/02/19200Southsam03/02/19200South
sam04/02/19220Southsam04/02/19220South
ken01/03/1970Westken01/03/1970West
ken02/03/19100Westken02/03/19100West
ken03/03/19120Westken03/03/19120West
ken03/04/19210Westken03/04/19210West
ken04/05/19220Westken04/05/19220West
ron01/04/1950NEron01/04/1950NE
ron01/16/19110NEron
an02/01/19200East
an03/05/19350East
an04/07/19370East
an04/10/19410East
an05/16/19450East
lena01/07/19100SW
lena02/09/19175SW
lena03/07/19225SW
lena05/01/19350SW

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
73
Hi Peter, I just worked a little longer with your suggested formula and it did work.
Question: is there a way to automatic fill the first row instead of typing manually the name of the person?
Thank you very much!
NamedateAmountLocation
john01/01/19
100North
2
02/01/19200North3
03/01/19500North4
#N/A#N/A#N/A#N/A

<colgroup><col style="mso-width-source:userset;mso-width-alt:4498;width:92pt" width="123"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,098,970
Messages
5,465,735
Members
406,447
Latest member
luckyeggcat

This Week's Hot Topics

Top