lookup

[Guest]

I need a formula that can help me do the following:

I have a pricing grid in the following format,

A B C D E
1 500 1000 2000 2500
2 64 $ 5 $ 6 $ 7 $ 8
3 80 $ 7 $ 8 $ 9 $10
4 96 $...............................
5 112 $...............................

The numbers in column A are the number pages
The numbers in row 1 are quantities of books

Is there a formula that would allow me to type in the number of pages, and
quantity, and provide me with the correct pricing? For example, a 64 page
book at a quantity of 500 equals $5. Also if a book contains 80 pages with a
quantity of 750, I need it to give me the next highest cost, which would be
$8. Any suggestions?

 

[Biff]

Try this:

A10 = lookup value pages
B10 = lookup value quantity

This will work as long as the quantity in B10 does not excede the max
quantity listed in the table. For example, the max qty in the table is 2500.
As long as the lookup value qty is not greater than 2500 this will work:

=VLOOKUP(A10,A1:E5,CEILING(B10/500,1)+1,0)

Biff

 

[Guest]

The only problem with this formula is that the qty that it goes up by jumps
from 5,000 to 7,500. So this formula would not work because qty will not
always increase in increments of 500. Is there another formula?

Thanks.

 

[Guest]

Try this:
Let's say your citeria in G1 and H1
G1 =80
H1 =750
Formuala in
I1
=IF(ISNA(MATCH(H1,A1:E1,0)),INDEX(A1:E5,MATCH(G1,A1:A5,0),MATCH(H1,A1:E1,1)+1),INDEX(A1:E5,MATCH(G1,A1:A5,0),MATCH(H1,A1:E1,0)))

 

[Guest]

Teethless mama,

I put your formula through, however, it gives me a NA. Any other
suggestions that may work. Thanks.

 

[Guest]

Tested and It works on my work sheet.

 

[Biff]

Well, if you don't tell us ALL the details all we can do is go by what you
have posted. Tell us what *ALL* the Qty values are.

Biff

 

[Guest]

Thanks for the formula it does work for certain quantities. When I put in a
quantity that matches to the quantity where the break even points are it
gives me an NA. For example, when I put in a quantity of 750 for an 80 page
book it gives me $8, but if I put in a quantity of 1,000 it gives me NA. Any
reasone why?

Thanks for you help.

 

[Guest]

nevermind. Looks like I accidentally use the incorrect cell. It works
great! Thanks for your help.