I am creating a worksheet which will make quotes for me on a type of USB
product we are going to launch.

The first thing is, I want to be able to have up to 3 different quantities
and prices on the quote (worksheet). Further along the worksheet, I have all
the prices set out in columns depending on the quantity bracket they fall in
(eg. 100, 250, 500, 1000, 1500, 2500, 5000)

If I enter a value in quantity 1 box that is between up to 100, or between
any of the other quantities mentioned above, I want the cell to be able to
lookup which price bracket it falls in based around the product capacity (ie.
128MB and 100pcs = £5.00)

I am not sure of which forumla type to use though, whether its an IF,
VLOOKUP or something else.

Can anyone give me some examples to try and assist me ??

Many Thanks,
Ben

You could set up your table and use vlookup with a MATCH formula to
determine your column
=vlookup(item,range,match column,false)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ben" wrote in message
...
I am creating a worksheet which will make quotes for me on a type of USB
product we are going to launch.

The first thing is, I want to be able to have up to 3 different quantities
and prices on the quote (worksheet). Further along the worksheet, I have
all
the prices set out in columns depending on the quantity bracket they fall
in
(eg. 100, 250, 500, 1000, 1500, 2500, 5000)

If I enter a value in quantity 1 box that is between up to 100, or between
any of the other quantities mentioned above, I want the cell to be able to
lookup which price bracket it falls in based around the product capacity
(ie.
128MB and 100pcs = £5.00)

I am not sure of which forumla type to use though, whether its an IF,
VLOOKUP or something else.

Can anyone give me some examples to try and assist me ??

Many Thanks,
Ben

On this occasion, a VLOOKUP is too restrictive for the argument.

I have tried this forumla but it doesn't appear to work.
=INDEX(AL19:AS28,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

Basically, what I want it to do is look at what is in B13 to determine what
the size capacity of the USB drive is, then look at what the quantity is that
I've typed in in B19 before referring to cell ranges AL19:AS28 which includes
column headers of the quantity brackets horizontally in Row 19 as well as the
sizes vertically and in all the other cells is the various prices.

For example, what the end result should be is based on a 128MB device, if
I've got 300 as the quantity that I've typed in, it should take the value
from the 250 column.

Hope this makes sense and that someone can assist with some suggestions.

"Don Guillett" wrote:

You could set up your table and use vlookup with a MATCH formula to
determine your column
=vlookup(item,range,match column,false)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ben" wrote in message
...
I am creating a worksheet which will make quotes for me on a type of USB
product we are going to launch.

The first thing is, I want to be able to have up to 3 different quantities
and prices on the quote (worksheet). Further along the worksheet, I have
all
the prices set out in columns depending on the quantity bracket they fall
in
(eg. 100, 250, 500, 1000, 1500, 2500, 5000)

If I enter a value in quantity 1 box that is between up to 100, or between
any of the other quantities mentioned above, I want the cell to be able to
lookup which price bracket it falls in based around the product capacity
(ie.
128MB and 100pcs = £5.00)

I am not sure of which forumla type to use though, whether its an IF,
VLOOKUP or something else.

Can anyone give me some examples to try and assist me ??

Many Thanks,
Ben

See Debra Dalgleish's site for some tips:
http://contextures.com/xlFunctions03.html

You'll want to use a single column/row in your =match() functions.

And you'll want to add something to that last =match() portion. You're missing
the first argument.


Ben wrote:

On this occasion, a VLOOKUP is too restrictive for the argument.

I have tried this forumla but it doesn't appear to work.
=INDEX(AL19:AS28,MATCH(B13,AM20:AS28,0),MATCH(AM19 :AS28,0))

Basically, what I want it to do is look at what is in B13 to determine what
the size capacity of the USB drive is, then look at what the quantity is that
I've typed in in B19 before referring to cell ranges AL19:AS28 which includes
column headers of the quantity brackets horizontally in Row 19 as well as the
sizes vertically and in all the other cells is the various prices.

For example, what the end result should be is based on a 128MB device, if
I've got 300 as the quantity that I've typed in, it should take the value
from the 250 column.

Hope this makes sense and that someone can assist with some suggestions.

"Don Guillett" wrote:

You could set up your table and use vlookup with a MATCH formula to
determine your column
=vlookup(item,range,match column,false)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ben" wrote in message
...
I am creating a worksheet which will make quotes for me on a type of USB
product we are going to launch.

The first thing is, I want to be able to have up to 3 different quantities
and prices on the quote (worksheet). Further along the worksheet, I have
all
the prices set out in columns depending on the quantity bracket they fall
in
(eg. 100, 250, 500, 1000, 1500, 2500, 5000)

If I enter a value in quantity 1 box that is between up to 100, or between
any of the other quantities mentioned above, I want the cell to be able to
lookup which price bracket it falls in based around the product capacity
(ie.
128MB and 100pcs = £5.00)

I am not sure of which forumla type to use though, whether its an IF,
VLOOKUP or something else.

Can anyone give me some examples to try and assist me ??

Many Thanks,
Ben






--

Dave Peterson