Help formatting - Index match with count

L

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
Hi all,

Please help...I'm trying to use an index match to move over pricing data. I did this last year but for some reason it's not working this year. It won't allow me to attach an example file so I will try to post everything below.

the formula:
=IF(INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,data1!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2))))=IF(INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,Sheet2!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$I$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2)))=0,"",INDEX(data1!$A$2:$K$10,MATCH(Sheet1!A2,data1!$A$2:$A$10,0),SMALL(IFERROR(COLUMN($A$1:$K$1)/(LEFT(data1!$A$1:$K$1,3)="Qty"),100000),COUNTIFS(A$2:A2,A2))))

That formula is for part A to move over quantities and then I would need the 2nd formula to move the pricing that goes with it. Neither formula is working now but I can post the 2nd formula if needed.

sheet1:
[TABLE="width: 467"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]sku[/TD]
[TD]_tier_price_website[/TD]
[TD]_tier_price_customer_group[/TD]
[TD]_tier_price_qty[/TD]
[TD]_tier_price_price[/TD]
[TD]TQ[/TD]
[TD]TP[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AK180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AB411FIR[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]54[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]47[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]43[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]AE120AUS[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]59.5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]56.5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]53.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]50[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]AA530[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5.5[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8.75[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]7.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]AL701ABI[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]26.75[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


data1:
[TABLE="width: 419"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item #/ Price Matrix[/TD]
[TD]Qty 2
2017[/TD]
[TD]Price 2
2017[/TD]
[TD]Qty 3
2017[/TD]
[TD]Price 3
2017[/TD]
[TD]Qty 4
2017[/TD]
[TD]Price 4
2017[/TD]
[TD]Qty 5
2017[/TD]
[TD]Price 5
2017[/TD]
[TD]Qty 6
2017[/TD]
[TD]Price 6
2017[/TD]
[/TR]
[TR]
[TD]AA530[/TD]
[TD]3[/TD]
[TD]7.60[/TD]
[TD]12[/TD]
[TD]7.40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA105[/TD]
[TD]3[/TD]
[TD]13.00[/TD]
[TD]12[/TD]
[TD]12.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA109GS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA110[/TD]
[TD]3[/TD]
[TD]13.95[/TD]
[TD]12[/TD]
[TD]12.95[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA115[/TD]
[TD]3[/TD]
[TD]16.00[/TD]
[TD]12[/TD]
[TD]15.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AE120AUS[/TD]
[TD]3[/TD]
[TD]15.45[/TD]
[TD]12[/TD]
[TD]14.50[/TD]
[TD]36[/TD]
[TD]13.50[/TD]
[TD]60[/TD]
[TD]12.75[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AA125[/TD]
[TD]3[/TD]
[TD]21.50[/TD]
[TD]12[/TD]
[TD]20.00[/TD]
[TD]36[/TD]
[TD]19.00[/TD]
[TD]60[/TD]
[TD]18.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AB411FIR[/TD]
[TD]3[/TD]
[TD]35.95[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AL701ABI[/TD]
[TD]3[/TD]
[TD]22.25[/TD]
[TD]12[/TD]
[TD]19.75[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


So with the data you can see there is a Qty then Price with 5 levels of that. The formula I posted was just for moving the Qty over then I would also need to move over Price.

I would like it to look like with this example:
[TABLE="width: 457"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]sku[/TD]
[TD]_tier_price_website[/TD]
[TD]_tier_price_customer_group[/TD]
[TD]_tier_price_qty[/TD]
[TD]_tier_price_price[/TD]
[TD]TQ[/TD]
[TD]TP[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]AK180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]AB411FIR[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35.95[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]51[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]47[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]43[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]AE120AUS[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]59.5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15.45[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]56.5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14.5[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]53.25[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]13.5[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]12.75[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD]AA530[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7.6[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]8.75[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]7.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]6.25[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD]AL701ABI[/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]26.75[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]22.25[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19.75[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]24[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD][/TD]
[TD]all[/TD]
[TD]all[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]22[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


I have an example file to better show this if anyone knows a way for it to allow me to attach it. It says I don't have positing permissions to do so.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

D
  • Solved
Index/match multiple criteria not working - HELP please
Replies
5
Views
326
NickFru
N
S
  • Solved
Table manipulations using dynamic arrays
2
Replies
10
Views
120
Fluff
Fluff
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
91
Fluff
Fluff
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
S
  • Solved
Possible in pivot?
Replies
2
Views
223
sharshra
S

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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