Search code examples
excelvbaindexingmatchsumproduct

Creating a two way lookup Price Matrix in Excel using VBA (Index/Match/SumProduct)

I'm trying to create a pricing matrix for products but using Index/Match or SumProduct formulas are proving to be a nightmare for me, I'm wondering if VBA would be easier?

Essentially if a product width or height is in between two figures, I need the price quoted to take on the next pricing bracket. (NB: Rounding up or using Ceiling functions within the formula doesn't work for me either)

Examples of the code I've tried using are:

=SUMPRODUCT(--(HeightRange=CEILING(Height,1000))*--(WidthRange=CEILING(Width,10))*PriceRange)

=INDEX(PriceRange,MATCH(MIN(ABS(HeightRange-Height)),ABS(HeightRange-Height),-1),MATCH(MIN(ABS(WidthRange-Width)),ABS(WidthRange-Width),-1))

Example Table: enter image description here

Solution

  • You could make use of =AGGREGATE() like so:

    =INDEX(A:E,AGGREGATE(15,3,(($A$4:$A$11>=N3)/($A$4:$A$11>=N3))*ROW($A$4:$A$11),1),AGGREGATE(15,3,(($B$3:$J$3>=M3)/($B$3:$J$3>=M3))*COLUMN($B$3:$J$3),1))