Index match with multiple criteria not working?? Please Help

S

scany42

New Member
Joined
Jun 4, 2013
Messages
20
So I need to do a look up. The information below is from two spreadsheets. The look-up equation goes where #n/a is, below Total 87171. My result needs to to be the 2031. I tried using an index-match equation, but it doesn't work for some reason. It might have to do the fact that Total 87171 comes from it being subtotaled? The charts below are abbreviated versions of much larger files (formatting is the same just a lot more stuff). I am stuck and I'd really appreciate any help. Thanks.

Or maybe it's because the equation doesn't tell excel where the Total sales file is located, like which drive and folder?

The chart in white is Total sales.
Customer name is in column E
Item Num (87171 Total) is in column M
2031 is in column P (16)

Chart in yellow
Customer name is in column B
87171 Total is the column heading (W1)
The information in yellow is in row 6


{=INDEX('[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$A$1:$X$23713,MATCH(B6&$W$1, '[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$E:$E&'[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$M:$M,0),16)}





[TABLE="width: 1421"]
<colgroup><col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" width="72"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;" width="190"> <col style="width: 149pt; mso-width-source: userset; mso-width-alt: 7277;" width="199"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 113pt; mso-width-source: userset; mso-width-alt: 5522;" width="151"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;" width="70"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" width="103"> <col style="width: 147pt; mso-width-source: userset; mso-width-alt: 7168;" width="196"> <col style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;" width="116"> <col style="width: 178pt; mso-width-source: userset; mso-width-alt: 8667;" width="237"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="width: 104, bgcolor: transparent"]InvoiceNumber[/TD]
[TD="width: 81, bgcolor: transparent"]InvoiceDate[/TD]
[TD="width: 72, bgcolor: transparent"]OrderDate[/TD]
[TD="width: 88, bgcolor: transparent"]DeliveryDate[/TD]
[TD="width: 190, bgcolor: transparent"]CustomerName[/TD]
[TD="width: 199, bgcolor: transparent"]Addr1[/TD]
[TD="width: 62, bgcolor: transparent"]ADDR2[/TD]
[TD="width: 151, bgcolor: transparent"]ADDRCITY[/TD]
[TD="width: 79, bgcolor: transparent"]ADDRSTATE[/TD]
[TD="width: 70, bgcolor: transparent"]ADDRZIP[/TD]
[TD="width: 103, bgcolor: transparent"]PONumber[/TD]
[TD="width: 196, bgcolor: transparent"]BuyingGroup[/TD]
[TD="width: 116, bgcolor: transparent"]ItemNum[/TD]
[TD="width: 237, bgcolor: transparent"]ItemDesc[/TD]
[TD="width: 81, bgcolor: transparent"]QuantityOrdered[/TD]
[TD="width: 64, bgcolor: transparent"]QuantityShipped[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1421"]
<colgroup><col style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" width="104"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;" width="72"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;" width="190"> <col style="width: 149pt; mso-width-source: userset; mso-width-alt: 7277;" width="199"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 113pt; mso-width-source: userset; mso-width-alt: 5522;" width="151"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;" width="70"> <col style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" width="103"> <col style="width: 147pt; mso-width-source: userset; mso-width-alt: 7168;" width="196"> <col style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;" width="116"> <col style="width: 178pt; mso-width-source: userset; mso-width-alt: 8667;" width="237"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="width: 104, bgcolor: transparent, align: right"]1133415[/TD]
[TD="class: xl65, width: 81, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, width: 88, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="width: 190, bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="width: 199, bgcolor: transparent"]333 50TH ST[/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 151, bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="width: 79, bgcolor: transparent"]MI[/TD]
[TD="width: 70, bgcolor: transparent, align: right"]495485639[/TD]
[TD="width: 103, bgcolor: transparent, align: right"]23107990[/TD]
[TD="width: 196, bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="width: 116, bgcolor: transparent, align: right"]87171[/TD]
[TD="width: 237, bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]40[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]40[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1140627[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]23161800[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]40[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1210399[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]23414120[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1153619[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]23194060[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1073545[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]22926960[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1026083[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]22752500[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1049314[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]22837910[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9955896[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]22505770[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9923905[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]00:00.0[/TD]
[TD="bgcolor: transparent"]GORDON FS/50TH STREET[/TD]
[TD="bgcolor: transparent"]333 50TH ST[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]GRAND RAPIDS[/TD]
[TD="bgcolor: transparent"]MI[/TD]
[TD="bgcolor: transparent, align: right"]495485639[/TD]
[TD="bgcolor: transparent, align: right"]22379470[/TD]
[TD="bgcolor: transparent"]GORDON FOODSERVICE[/TD]
[TD="bgcolor: transparent, align: right"]87171[/TD]
[TD="bgcolor: transparent"]T&S Burry Plain Premium Bagels[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]87171 Total[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2031[/TD]
[/TR]
</tbody>[/TABLE]






[TABLE="width: 1155"]
<colgroup><col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" width="67"> <col style="width: 141pt; mso-width-source: userset; mso-width-alt: 6875;" width="188"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;" width="100"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;" width="25"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" span="2" width="46"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;" width="50"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" width="54"> <col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <col style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" span="2" width="52"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;" width="43"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="4" width="68"> <tbody>[TR]
[TD="class: xl66, width: 67, bgcolor: #BFBFBF"]Customer #[/TD]
[TD="class: xl66, width: 188, bgcolor: #BFBFBF"]Customer Name[/TD]
[TD="class: xl66, width: 100, bgcolor: #BFBFBF"]City[/TD]
[TD="class: xl66, width: 25, bgcolor: #BFBFBF"]ST[/TD]
[TD="class: xl66, width: 46, bgcolor: #BFBFBF"]TY LBS[/TD]
[TD="class: xl67, width: 46, bgcolor: #BFBFBF"]LY LBS[/TD]
[TD="class: xl67, width: 50, bgcolor: #BFBFBF"]LB Diff[/TD]
[TD="class: xl67, width: 80, bgcolor: #BFBFBF"]% Change LBS[/TD]
[TD="class: xl67, width: 54, bgcolor: #BFBFBF"]TY Cases[/TD]
[TD="class: xl67, width: 53, bgcolor: #BFBFBF"]LY Cases[/TD]
[TD="class: xl67, width: 62, bgcolor: #BFBFBF"]Cases Diff[/TD]
[TD="class: xl67, width: 95, bgcolor: #BFBFBF"]% Change Cases[/TD]
[TD="class: xl67, width: 52, bgcolor: #BFBFBF"]TY $[/TD]
[TD="class: xl67, width: 52, bgcolor: #BFBFBF"]LY $[/TD]
[TD="class: xl67, width: 56, bgcolor: #BFBFBF"]$ Diff[/TD]
[TD="class: xl68, width: 68, bgcolor: #BFBFBF"]% Change $[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"][/TD]
[TD="class: xl65, width: 43, bgcolor: transparent, align: right"][/TD]
[TD="class: xl65, width: 68, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"][/TD]
[TD="class: xl70, width: 68, bgcolor: transparent"]87171 Total[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1155"]
<colgroup><col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" width="67"> <col style="width: 141pt; mso-width-source: userset; mso-width-alt: 6875;" width="188"> <col style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;" width="100"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;" width="25"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" span="2" width="46"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;" width="50"> <col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;" width="54"> <col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" width="62"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <col style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" span="2" width="52"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" width="68"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;" width="43"> <col style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="4" width="68"> <tbody>[TR]
[TD="class: xl67, width: 67, bgcolor: yellow"]10690-3[/TD]
[TD="class: xl67, width: 188, bgcolor: yellow"]GORDON FS/50TH STREET[/TD]
[TD="class: xl67, width: 100, bgcolor: yellow"]GRAND RAPIDS[/TD]
[TD="class: xl67, width: 25, bgcolor: yellow"]MI[/TD]
[TD="class: xl68, width: 46, bgcolor: yellow, align: right"]295,412[/TD]
[TD="class: xl69, width: 46, bgcolor: yellow, align: right"]224,415[/TD]
[TD="class: xl69, width: 50, bgcolor: yellow, align: right"]70,997[/TD]
[TD="class: xl70, width: 80, bgcolor: yellow, align: right"]31.64%[/TD]
[TD="class: xl69, width: 54, bgcolor: yellow, align: right"]21,521[/TD]
[TD="class: xl69, width: 53, bgcolor: yellow, align: right"]16,237[/TD]
[TD="class: xl69, width: 62, bgcolor: yellow, align: right"]5,284[/TD]
[TD="class: xl70, width: 95, bgcolor: yellow, align: right"]32.54%[/TD]
[TD="class: xl71, width: 52, bgcolor: yellow, align: right"]$592,352[/TD]
[TD="class: xl71, width: 52, bgcolor: yellow, align: right"]$429,930[/TD]
[TD="class: xl71, width: 56, bgcolor: yellow, align: right"]$162,422[/TD]
[TD="class: xl72, width: 68, bgcolor: yellow, align: right"]37.78%[/TD]
[TD="class: xl73, width: 64, bgcolor: yellow, align: right"][/TD]
[TD="class: xl73, width: 64, bgcolor: yellow, align: right"][/TD]
[TD="class: xl73, width: 43, bgcolor: yellow, align: right"][/TD]
[TD="class: xl73, width: 68, bgcolor: yellow"][/TD]
[TD="class: xl73, width: 68, bgcolor: yellow"] [/TD]
[TD="class: xl73, width: 68, bgcolor: yellow"] [/TD]
[TD="class: xl73, width: 68, bgcolor: yellow, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
This worked for me:

=INDEX('[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$A$3:$X$23713,MATCH(1,INDEX(('[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$E$2:$E$23712=B6)*('[Total Sales 04 1 13 to 06 30 13 TARGET CUSTOMERS.xlsx]Customer'!$M$3:$M$23713=$W$1),),FALSE),16)

You need to offset the ranges because the Customer Name doesn't appear against the Total.
 
Upvote 0
You must log in or register to reply here.

Similar threads

subrosumon
  • Question Question
Multiple Sheet pivot table
Replies
1
Views
834
steve the fish
steve the fish
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
M
  • Question Question
Index Match formula with multiple criteria
Replies
5
Views
778
DanteAmor
DanteAmor
C
  • Question Question
Hours calculation
Replies
7
Views
937
lezawang
L
C
  • Question Question
SUMIF - Based On Unique Values and Multiple Criteria
2 3
Replies
25
Views
11K
bronxjm
B

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

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