lookup

[Sum Limit and marking]

What would be the best formula to use for the following task:

I have a list of prices, organized by pages and quantity:

Qty 100 200 300 400 .....
Pages
64 $ $ $ $
96 $ $ $ $
128

What formula would allow me to retrieve pricing by Pages. For example I
want to see what the prices would be for pages of 64's?

 

[Ron Coderre]

Try something like this:

With
Sheet1!A1:Z100 containing your pricing table

then...on Sheet2

A1: (a page value)
B1: (a quantity)

This formula finds the Page referenced in A1 and returns the value under the
Quantity referenced in B1
C1: =VLOOKUP(A1,Sheet1!$A$1:$Z$100,MATCH(B1,Sheet1!$A$1:$Z$1,0),0)

Is that something you can work with?

Note: the formula is looking for exact matches.
Is there any chance that the quantities may be non-listed values?

***********
Regards,
Ron

XL2002, WinXP

 

[Biff]

One way:

A10 = pages
B10 = Qty

=INDEX(B2:E4,MATCH(A10,A2:A4),MATCH(B10,B1:E1))

If pages and/or Qty is lower than the lowest value of the table you'll get
#N/A

For example: 50 pages or a Qty of 75.

Biff

 

[Sum Limit and marking]

Ron,

Yes, if a qty of 75 is specified then I need it to round up to 100 pricing.
Or a qty of 201 needs to be rounded up to 300 pricing. Will the formula you
provided to me still work?

thanks