Complex macro to pull price by giving matching 4 criteria?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,

Really stuck on this

I have to find a product price for the data I have in sheet "Products" from Sheet "Prices"

any I don't find I need a "No Match" input to the cell!

heres what I need to do

the products are described using 4 columns in sheet Products

AA,SKU
AB,size
AC,Num Pages
AD Quantity
AE is where I want to return a price

so heres my problems

Sheet Prices

Has "SKU" in Column A, so fist we need to match the SKU, but there will be more than One row that matches
Product size In Column B, again not unique

the we have Number of Pages and this is listed as a number in Sheet Products but a range using two columns in sheet Prices
so heres what I need to be able to look at the page size and see which row it fits in by page size

I will do I diagram below to make this simpler

and the same with quanity.

how can I look up the price>

Visial Diagrams

Sheet Products (there is a lot more data then in this example!)

AA
AB
AC
AD
AE
AF
1
SKU
Prod size
Num Pages
Quantity
Price?
AE is what i'm trying to find
2
11122233310x12
10
1
£6 (this is what I need to find!)
matchs row 4
3
222333444
10x12
10
1
No Match
No Match
4
111222333
10x12
25
15
£12
matchs row 7<strike></strike>
5
111222333
12x12
25
1
etc all the way down the rows
6
222333444
12x12
10
7
7
555555555
10x10
10
7
8
555555555
12x12
25
1
9
111222333
10x12
10
15

<tbody>
</tbody>
Sheet Prices
A
B
C
D
E
F
G
H
I
J
K
1
SKU
Prod Size
Pages From
Pages To
Quantity From
Quantity To
Price
2
111222333
10x10
1
100
1
100
£5
3
111222333
10x12
1
7
1
100
£4
4
111222333
10x12
8
15
1
5
£6
5
111222333
10x12
8
15
6
100
£9
6
111222333
10x12
16
100
1
10
£10
7
111222333
10x12
16
100
11
100
£12
8
9
10
11
12
13
14

<tbody>
</tbody>

I hope this gives an example of what I need happy to add any extra rows if needed

please help if you can

Tony
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Adjust all the $10 values in the formula to be something big enough to cover all the data rows in 'Prices'


Book1
AAABACADAE
1SKUProd sizeNum PagesQuantityPrice?
211122233310x121016
322233344410x12101No Match
411122233310x12251512
511122233312x12251No Match
622233344412x12107No Match
755555555510x10107No Match
855555555512x12251No Match
911122233310x1210159
Products
Cell Formulas
RangeFormula
AE2=IFERROR(AGGREGATE(14,6,Prices!G$2:G$10/((Prices!A$2:A$10=AA2)*(Prices!B$2:B$10=AB2)*(Prices!C$2:C$10<=AC2)*(Prices!D$2:D$10>=AC2)*(Prices!E$2:E$10<=AD2)*(Prices!F$2:F$10>=AD2)),1),"No Match")
 
Last edited:
Upvote 0
You can youse a formula with SUMIFS or SUMPRODUCT.
Example: =SUMPRODUCT((A2=AA2:AA9)*(B2=AB2:AB9)*C2>=AC2:AC9)*(C2<=AC2:AC9)*(D2>=AD2:AD9)*(D2<=AD2:AD9),H4:H9)
 
Upvote 0
Hi Peter,
Thank you very much, does the job great :)
thanks very much

Tony
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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