Issue in Index formula

Z

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
I have pivot table at SKU level A1:C2, than same expended with including Customer Name D1:D1000 but here I have shown till line 50, pivot table are changing monthly based on data input.

In the result area line 56 I have applied following formula which is working fine from A59:C81
=IF($E6<>$A$6,"",INDEX($E$6:$H$50,$D59,2))

If copying same in A87 it is showing blank because index is picking from E34, what changes I need to do to get data for "HyoJet 68 208 ltr" and 3rd, 4th SKU information as shown for 1st SKU in result area ?


YTD No.90YTD No.90
Grade(All)Grade(All)
Year2A2021Year2A2021
SKUSum of VolumeSum of UNPSKUCustomer NameSum of VolumeSum of UNP
EngJet LA 208 ltr168,201.00451.18EngJet LA 208 ltrCustomer 107
22,165​
442​
HyoJet 68 208 ltr165,872.00518.92EngJet LA 208 ltrCustomer 195
21,428​
444​
NmoPlus 444 Bulk133,847.00781.96EngJet LA 208 ltrCustomer 196
19,609​
439​
GetJet 140GL4 208 ltr132,962.00581.64EngJet LA 208 ltrCustomer 191
18,501​
467​
GetJet 140GL4 Bulk122,461.00643.09EngJet LA 208 ltrCustomer 114
16,354​
450​
SetJet 50 CD 1 ltr121,011.00437.89EngJet LA 208 ltrCustomer 192
14,320​
479​
NmoPlus 444 208 ltr102,021.00688.68EngJet LA 208 ltrCustomer 108
13,854​
442​
BetJet 20W50 CNG CD Bulk101,777.00606.53EngJet LA 208 ltrCustomer 193
11,786​
475​
SetJet 50 CD Bulk96,670.00275.84EngJet LA 208 ltrCustomer 197
9,205​
421​
ArfUltra 11 208 ltr91,602.00618.75EngJet LA 208 ltrCustomer 109
7,843​
468​
MrnJet75 208 ltr90,443.00662.44EngJet LA 208 ltrCustomer 194
6,863​
474​
ArfJet7 208 ltr63,273.00542.71EngJet LA 208 ltrCustomer 123
3,332​
405​
NetJet 5W30 SG 208 ltr56,204.00554.74EngJet LA 208 ltrCustomer 110
2,941​
421​
ArfPlus9 208 ltr54,285.00586.67HyoJet 68 208 ltrCustomer 171
18,709​
521​
BetJet 20W50 CNG CD 4 ltr49,071.00547.98HyoJet 68 208 ltrCustomer 179
16,030​
526​
HyoJet 68 208 ltrCustomer 169
15,335​
520​
HyoJet 68 208 ltrCustomer 170
14,457​
520​
HyoJet 68 208 ltrCustomer 176
13,248​
526​
HyoJet 68 208 ltrCustomer 168
12,700​
520​
HyoJet 68 208 ltrCustomer 167
12,100​
516​
HyoJet 68 208 ltrCustomer 178
11,224​
521​
HyoJet 68 208 ltrCustomer 172
10,699​
521​
HyoJet 68 208 ltrCustomer 177
10,682​
526​
HyoJet 68 208 ltrCustomer 174
10,524​
493​
HyoJet 68 208 ltrCustomer 175
8,614​
522​
HyoJet 68 208 ltrCustomer 173
6,071​
493​
HyoJet 68 208 ltrCustomer 197
5,479​
521​
NmoPlus 444 BulkCustomer 145
12,793​
800​
NmoPlus 444 BulkCustomer 119
12,024​
797​
NmoPlus 444 BulkCustomer 120
10,913​
784​
NmoPlus 444 BulkCustomer 122
10,855​
778​
NmoPlus 444 BulkCustomer 123
10,028​
785​
NmoPlus 444 BulkCustomer 106
9,482​
720​
NmoPlus 444 BulkCustomer 147
9,116​
802​
NmoPlus 444 BulkCustomer 124
8,966​
800​
NmoPlus 444 BulkCustomer 148
8,173​
804​
NmoPlus 444 BulkCustomer 105
8,132​
798​
NmoPlus 444 BulkCustomer 144
7,211​
800​
NmoPlus 444 BulkCustomer 104
6,314​
801​
NmoPlus 444 BulkCustomer 146
4,762​
682​
NmoPlus 444 BulkCustomer 100
4,713​
801​
NmoPlus 444 BulkCustomer 150
4,604​
782​
NmoPlus 444 BulkCustomer 99
3,202​
798​
NmoPlus 444 BulkCustomer 190
2,559​
623​
GetJet 140GL4 208 ltrCustomer 107
16,115​
593​
Result area
Unit Proceed & volumeEngJet LA 208 ltr
1​
Customer NameVolumeVolume & UNPSerial
Customer 10722,165442
1​
Customer 19521,428444
2​
Customer 19619,609439
3​
Customer 19118,501467
4​
Customer 11416,354450
5​
Customer 19214,320479
6​
Customer 10813,854442
7​
Customer 19311,786475
8​
Customer 1979,205421
9​
Customer 1097,843468
10​
Customer 1946,863474
11​
Customer 1233,332405
12​
Customer 1102,941421
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
Unit Proceed & volumeHyoJet 68 208 ltr
2​
Customer NameVolumeVolume & UNP
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​
19​
20​
21​
22​
23​
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Why don't you use a pivot table for the result.
 
Upvote 0
There is a standard template from Global performance team to fill from Pivot table with Automation so wanted to fix 1 time formula to bring info from the changing results from Pivot table.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Easiest way to get total.
Replies
3
Views
286
awoohaw
awoohaw
Jaafar Tribak
  • Question
Cool Class for making Floating Shapes !
2
Replies
15
Views
4K
Jaafar Tribak
Jaafar Tribak
S
  • Question
Filter the values.
Replies
6
Views
620
sekar_r24
S
R
  • Question
Help!
Replies
3
Views
1K
63falcondude
63falcondude

Forum statistics

Threads
1,215,425
Messages
6,124,824
Members
449,190
Latest member
rscraig11

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