Pricing lookup

[scott]

I have a pricing table set up by product,billingplan,mintransactions and
price.

I am stuggling with how to do two lookups to get the pricing structure
for a product and billing plan. I keep thinking I would need to find
the range for Product 1, then do a lookup on the Billing plan within
that range to return the pricing table for Product1 on standard billing

Any ideas where to start would be appreciated

ProdID BPlan MinTran Price
1 std 0 10
1 std 1001 9
1 std 2001 8
1 ws 0 8
1 ws 5001 7
1 ws 10001 3
2
2
2

 

[Bernard V Liengme]

Hi Scott,

It would be so much easier if the table was in the form

std ws
1 $4 $5
2 $6 $7

Then you use Match to locate the position of the Product and of the Plan
and combine these with Index to get the price. Any chance the data can be
changed? If so we can get more specific. Otherwise it looks like VBA is the
only answer.
Bernard

 

[scott]

Thanks Bernard. I will see what I can do about manipulating the data.
How big of a challenge is the VBA solution?