Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
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 "Sum Limit and marking" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
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 "Ron Coderre" wrote: 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 "Sum Limit and marking" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I isolate a lookup vectors but not values from autofill? | Excel Worksheet Functions | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |