I need help with a spreadsheet I'm working on. I created a dropdown menu
where the user can choose the size and the pressure. How would I get the
appropriate cost to show up with those parameters? For example, say I wanted
to use the 1.25" size with a pressure of 6000psi. What formula do I need to
use in order for $13563 to show up? Any help would be appreciated.


Size Pressure SAP $
1.25 0 - 5,000 380787 7628
5,001 - 7,500 380787 13563
7,501 - 10,000 380787 17978
10,001 - 12,500 380787 23516
12,501 - 15,000 380787 Quoted

1.5 0 - 5,000 380787 8297
5,001 - 7,500 380787 15192
7,501 - 10,000 380787 20553
10,001 - 12,500 380787 26735
12,501 - 15,000 380787 Quoted

To make it "easy" reconstruct your table(s) into a single table using a flat
database format. Like this:

....A........B..........C........D..........E....
1.25...0..........5000.....xxx.....7628
1.25...5001....7500.....xxx.....13563
1.25...7501....10000...xxx.....17978
1.50...0..........5000.....xxx.....8297
1.50...5001....7500.....xxx.....15192

Then, to get the price for:

G1 = 1.25
H1 = 6000

Array entered** :

=INDEX(E1:E5,MATCH(1,(A1:A5=G1)*(H1=B1:B5)*(H1<=C 1:C5),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"deb15qt" wrote in message
...
I need help with a spreadsheet I'm working on. I created a dropdown menu
where the user can choose the size and the pressure. How would I get the
appropriate cost to show up with those parameters? For example, say I
wanted
to use the 1.25" size with a pressure of 6000psi. What formula do I need
to
use in order for $13563 to show up? Any help would be appreciated.


Size Pressure SAP $
1.25 0 - 5,000 380787 7628
5,001 - 7,500 380787 13563
7,501 - 10,000 380787 17978
10,001 - 12,500 380787 23516
12,501 - 15,000 380787 Quoted

1.5 0 - 5,000 380787 8297
5,001 - 7,500 380787 15192
7,501 - 10,000 380787 20553
10,001 - 12,500 380787 26735
12,501 - 15,000 380787 Quoted