Vlookup - multiple columns

V

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
90
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
NameDateAmountRegion NameDateAmountRegion
JohnNorth JohnNorth
JohnNorth JohnNorth
JohnNorth JohnNorth
JohnNorth JohnNorth
EdSouth EdSouth
EdSouth EdSouth
EdSouth EdSouth
EdSouth EdSouth
AdamWest AdamWest
AdamWest AdamWest
AdamWest AdamWest
AdamWest AdamWest
NameDateAmountRegion NameDateAmountRegion
JohnNorth JohnNorth
JohnNorth JohnNorth
JohnSouth JohnNorth
JohnSouth JohnNorth
EdSouth EdSouth
EdSouth EdSouth
EdSouth Ed#N/A#N/A#N/A
Ed#N/A#N/A#N/A Ed#N/A#N/A#N/A
AdamWest Adam#N/A#N/A#N/A
AdamWest Adam#N/A#N/A#N/A
AdamWest Adam#N/A#N/A#N/A
AdamWest 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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]600[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]150[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]150[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]250[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]170[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]170[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]600[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]800[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]800[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]600[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]100[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]23[/TD]

[TD="align: right"]02/02/19[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]150[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]24[/TD]

[TD="align: right"]03/03/19[/TD]
[TD="align: right"]150[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]25[/TD]

[TD="align: right"]04/05/19[/TD]
[TD="align: right"]250[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]28[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]29[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]30[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]31[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]32[/TD]

[TD="align: right"]01/01/19[/TD]
[TD="align: right"]350[/TD]

</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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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))

Name
johnNorth
johnNorth
johnNorth
samSouth
samSouth
samSouth
samSouth
kenWest
kenWest
kenWest
kenWest
kenWest
ronNE
ronNE
anEast
anEast
anEast
anEast
anEast
lenaSW
lenaSW
lenaSW
lenaSW

<tbody>
[TD="class: xl65, width: 68"]date[/TD]
[TD="width: 64"]Amount[/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 123"]Name[/TD]
[TD="class: xl65, width: 64"]date[/TD]
[TD="width: 64"]Amount[/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]john[/TD]

[TD="class: xl65, align: right"]01/01/19[/TD]
[TD="align: right"]100[/TD]

[TD="class: xl67"]john[/TD]
[TD="class: xl67"]01/01/19[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]North[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="class: xl67"]john[/TD]
[TD="class: xl67"]02/01/19[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]North[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/01/19[/TD]
[TD="align: right"]500[/TD]

[TD="class: xl67"]john[/TD]
[TD="class: xl67"]03/01/19[/TD]
[TD="class: xl66"]500[/TD]
[TD="class: xl66"]North[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]01/02/19[/TD]
[TD="align: right"]50[/TD]

[TD="class: xl67"]sam[/TD]
[TD="class: xl67"]01/02/19[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]South[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/02/19[/TD]
[TD="align: right"]150[/TD]

[TD="class: xl67"]sam[/TD]
[TD="class: xl67"]02/02/19[/TD]
[TD="class: xl66"]150[/TD]
[TD="class: xl66"]South[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/02/19[/TD]
[TD="align: right"]200[/TD]

[TD="class: xl67"]sam[/TD]
[TD="class: xl67"]03/02/19[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]South[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]04/02/19[/TD]
[TD="align: right"]220[/TD]

[TD="class: xl67"]sam[/TD]
[TD="class: xl67"]04/02/19[/TD]
[TD="class: xl66"]220[/TD]
[TD="class: xl66"]South[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]01/03/19[/TD]
[TD="align: right"]70[/TD]

[TD="class: xl67"]ken[/TD]
[TD="class: xl67"]01/03/19[/TD]
[TD="class: xl66"]70[/TD]
[TD="class: xl66"]West[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]02/03/19[/TD]
[TD="align: right"]100[/TD]

[TD="class: xl67"]ken[/TD]
[TD="class: xl67"]02/03/19[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]West[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/03/19[/TD]
[TD="align: right"]120[/TD]

[TD="class: xl67"]ken[/TD]
[TD="class: xl67"]03/03/19[/TD]
[TD="class: xl66"]120[/TD]
[TD="class: xl66"]West[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]03/04/19[/TD]
[TD="align: right"]210[/TD]

[TD="class: xl67"]ken[/TD]
[TD="class: xl67"]03/04/19[/TD]
[TD="class: xl66"]210[/TD]
[TD="class: xl66"]West[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]04/05/19[/TD]
[TD="align: right"]220[/TD]

[TD="class: xl67"]ken[/TD]
[TD="class: xl67"]04/05/19[/TD]
[TD="class: xl66"]220[/TD]
[TD="class: xl66"]West[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]01/04/19[/TD]
[TD="align: right"]50[/TD]

[TD="class: xl67"]ron[/TD]
[TD="class: xl67"]01/04/19[/TD]
[TD="class: xl66"]50[/TD]
[TD="class: xl66"]NE[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65, align: right"]01/16/19[/TD]
[TD="align: right"]110[/TD]

[TD="class: xl67"]ron[/TD]
[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]02/01/19[/TD]
[TD="align: right"]200[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]03/05/19[/TD]
[TD="align: right"]350[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]04/07/19[/TD]
[TD="align: right"]370[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]04/10/19[/TD]
[TD="align: right"]410[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]05/16/19[/TD]
[TD="align: right"]450[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]01/07/19[/TD]
[TD="align: right"]100[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]02/09/19[/TD]
[TD="align: right"]175[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]03/07/19[/TD]
[TD="align: right"]225[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl65, align: right"]05/01/19[/TD]
[TD="align: right"]350[/TD]

[TD="class: xl65"][/TD]

</tbody>

<tbody>
[TD="class: xl63"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 123"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl65"][/TD]
[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

[TD="class: xl63, align: right"][/TD]
[TD="align: right"][/TD]

[TD="class: xl63"][/TD]

</tbody>
 
Last edited:
Upvote 0
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!
john

<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>
[TD="width: 123"]Name[/TD]
[TD="class: xl65, width: 64"]date[/TD]
[TD="width: 64"]Amount[/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[TD="class: xl67"]01/01/19
[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl67"]North
[/TD]
[TD="class: xl66"][/TD]
[TD="align: right"]2
[/TD]

[TD="class: xl68"] [/TD]
[TD="class: xl67"]02/01/19[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl67"]North[/TD]
[TD="class: xl66"][/TD]
[TD="align: right"]3[/TD]

[TD="class: xl68"] [/TD]
[TD="class: xl67"]03/01/19[/TD]
[TD="class: xl66"]500[/TD]
[TD="class: xl67"]North[/TD]
[TD="class: xl66"][/TD]
[TD="align: right"]4[/TD]

[TD="class: xl67"]#N/A[/TD]
[TD="class: xl66"]#N/A[/TD]
[TD="class: xl67"]#N/A[/TD]
[TD="class: xl66"][/TD]
[TD="align: center"]#N/A[/TD]

</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VLOOKUP?
Replies
1
Views
213
MeaclH
M
K
  • Solved
COUNTIFS formula.
2
Replies
14
Views
606
Kayslover
K
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
252
MeaclH
M
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
863
SaraWitch
SaraWitch

Forum statistics

Threads
1,223,929
Messages
6,175,457
Members
452,643
Latest member
gjcase

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