Look up one value and return multiple corresponding values and copy data from multiple sheets to one sheet (excluding the blank rows

C

Carguy37122

New Member
Joined
Sep 11, 2014
Messages
17
I hope someone can assist me with this excel/VBA question.

The project I am working on compares a list of VIN#s (this worksheet is called Seller List) to historical sales data (this worksheet is called 2014 Sales Matrix) based on the 1st 8 digits of the VIN#s.
I've created 49 sheets that reference a single vin# from the Seller List worksheet. Each of these worksheets are named 2, 3, 4, 5, thru 50.

My goal is to be able to paste random vin#s in the "Seller List" and produce a summary of sales data from 2014 sorted by buyer that lists buyers who purchase similar vehicles. I would like to be able to email or fax the specific VIN# to the potential buyers. So sheet 2 could have 4 potential buyers, while sheet 3 could have 39 potential buyers, etc...

The 2014 Sales Matrix data is updated weekly, so the results will vary based on VIN# and quantity of sales.

I've added examples of the worksheets below. I'm sure there is an easier way to pull this data, but I'm not the best when it comes to excel. Thanks in advance!

sample of 2014 Sales Matrix
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]VIN# - First 8 Digits[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Buyer ID[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 391"]
<tbody>[TR]
[TD]Buyer Name[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 252"]
<tbody>[TR]
[TD]Buyer Representative[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD]Buyer Phone[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD]Buyer Fax#[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD]Buyer Email[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]Internet Buyer?[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 419"]
<tbody>[TR]
[TD]Buyer Street[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 215"]
<tbody>[TR]
[TD]Buyer City[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 199"]
<tbody>[TR]
[TD]Buyer State[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Buyer Zip[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD]Sold Date[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 263"]
<tbody>[TR]
[TD]Location[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD]Sale Price[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 201"]
<tbody>[TR]
[TD]VIN[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD]Mileage[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Asking Price[/TD]
[TD][TABLE="width: 179"]
<tbody>[TR]
[TD]Status[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 575"]
<tbody>[TR]
[TD]Description[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD]Location ID[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]W/O[/TD]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]Venue[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 172"]
<tbody>[TR]
[TD]Venue Code[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Days Offered[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Highest Offering[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 288"]
<tbody>[TR]
[TD]Title Status[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 163"]
<tbody>[TR]
[TD]Blue[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Green[/TD]
[TD]Red[/TD]
[TD]Yellow[/TD]
[TD][TABLE="width: 391"]
<tbody>[TR]
[TD]Seller Name[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD]Seller Address1[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD]Seller City[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]Seller State[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]Seller Zip[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD]Days Run[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 163"]
<tbody>[TR]
[TD]Vehicle Grade[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]1G6DC5EY[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1234567[/TD]
[TD]Trucks are fun[/TD]
[TD]Steve Jobs[/TD]
[TD]999-999-1234[/TD]
[TD]999-999-2234[/TD]
[TD]bigdaddy@trucksjob.com[/TD]
[TD]yes[/TD]
[TD]1234 Granny Smith Lane[/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD]32124[/TD]
[TD]04/02/2014[/TD]
[TD]Internet Sale[/TD]
[TD]19000[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]1G6DC5EYhwyeu182hs8abe988s8[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]54000[/TD]
[TD]21000[/TD]
[TD]Sold[/TD]
[TD][TABLE="width: 575"]
<tbody>[TR]
[TD]SALME114hwyeu182hs8abe988s8 2004 LAND ROVER RANGE ROVER - [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]SDEW[/TD]
[TD]64372[/TD]
[TD]Open Sale[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]19000[/TD]
[TD]Title sent to buyer[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]no[/TD]
[TD]abc motors[/TD]
[TD]3322 main street[/TD]
[TD]San Diego[/TD]
[TD]CA[/TD]
[TD]11111[/TD]
[TD]4[/TD]
[TD]2.8[/TD]
[/TR]
[TR]
[TD]1G1PE5SB[/TD]
[TD]6573332[/TD]
[TD]cars for peeps[/TD]
[TD]Riley Johnson[/TD]
[TD]345-543-2345[/TD]
[TD]345-543-3321[/TD]
[TD]carjohnson@carguyjohnson.com[/TD]
[TD]no[/TD]
[TD]3453 Long Street[/TD]
[TD]Tampa[/TD]
[TD]FL[/TD]
[TD]33311[/TD]
[TD]09/022/2014[/TD]
[TD]Lot Sale[/TD]
[TD]7000[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]1G1PE5SBjf3839synhdoa837ues[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]102000[/TD]
[TD]8000[/TD]
[TD]Sold/Funds Not Sent[/TD]
[TD][TABLE="width: 575"]
<tbody>[TR]
[TD]3N1BC13Ejf3839synhdoa837ues 2009 NISSAN VERSA - [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]TAMF[/TD]
[TD]188283[/TD]
[TD]Open Sale[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]7000[/TD]
[TD]Title pending funding[/TD]
[TD]no[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD]no[/TD]
[TD]xyz motors[/TD]
[TD]9283 Circle S Drive[/TD]
[TD]Tampa[/TD]
[TD]FL[/TD]
[TD]23212[/TD]
[TD]2[/TD]
[TD]3.8[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 7492"]
<tbody>[TR]
[TD]
Sample of Seller List
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 197"]
<tbody>[TR]
[TD]VIN#[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 113"]
<tbody>[TR]
[TD]VIN# - 8 Digits[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD]Year[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD]Make[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]Model[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD]Trim[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD]Miles[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]# of Matches (formula listed)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 197"]
<tbody>[TR]
[TD]1G6DC5EY3r2537541[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1G6DC5EY[/TD]
[TD]2011[/TD]
[TD]Cadillac[/TD]
[TD]CTS[/TD]
[TD][/TD]
[TD]33,654[/TD]
[TD]=COUNTIFS('2014 Sales Matrix'!$A:$A,$B2) [/TD]
[/TR]
[TR]
[TD][TABLE="width: 197"]
<tbody>[TR]
[TD]1G1PE5SB3f6374832[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]1G1PE5SB[/TD]
[TD]2014[/TD]
[TD]Chevrolet[/TD]
[TD]Cruze[/TD]
[TD][/TD]
[TD]25,304[/TD]
[TD]=COUNTIFS('2014 Sales Matrix'!$A:$A,$B3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Sample of worksheet named 2 (worksheets 2-50 are formatted the same way)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD]1 potential buyer(s) for 1G6DC5EY5B0149272 2011 CADILLAC CTS 35958 Miles[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD]VIN#[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]Row#[/TD]
[TD][TABLE="width: 275"]
<tbody>[TR]
[TD]Buyer[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Rep Name[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD]Tel#[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 171"]
<tbody>[TR]
[TD]Address[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD]City[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD]State[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 55"]
<tbody>[TR]
[TD]Zip[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]Purchase Date[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD]Sale Price[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD] Miles [/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 89"]
<tbody>[TR]
[TD]Grade[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD]1G6DC5EY[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="align: right"]1624[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]sell cars motors[/TD]
[TD]steve smith[/TD]
[TD]394-239-3301[/TD]
[TD]329 2nd street[/TD]
[TD]Nashville[/TD]
[TD]TN[/TD]
[TD]37112[/TD]
[TD]02/14/2014[/TD]
[TD]15,000[/TD]
[TD]83000[/TD]
[TD]3.9[/TD]
[/TR]
[TR]
[TD]('SELLER LIST'!$B$2)[/TD]
[TD]=IF(ISERROR(SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))),"",SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1)))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,3)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,3))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,4)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,4))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,5)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,5))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,9)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,9))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,10)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,10))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,11)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,11))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,12)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,12))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,13)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,13))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,15)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,15))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,17)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,17))[/TD]
[TD]=IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$AL$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,38)),"",INDEX('2014 Sales Matrix'!$A$2:$AL$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,38))[/TD]
[/TR]
</tbody>[/TABLE]





[/TD]
[TD]



[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You must log in or register to reply here.

Similar threads

P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
194
Pbobas
P
T
  • Question Question
Copy row values from one worksheet in one workbook to multiple workbooks based on file name
Replies
0
Views
309
thechad
T
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
290
mumps
mumps
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
196
ngochien251088
N
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
106
sijpie
sijpie

Forum statistics

Threads
1,223,718
Messages
6,174,082
Members
452,542
Latest member
Bricklin

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