Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

You don't need a table for your example. If the age is entered in cell A! then

=(A1=10)*(A1<=20)*3+(A1=21)*(A1<=30)*4+(A1=31)* 5

will yield the correct dollar amount
--
Gary's Student


"jw446" wrote:

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

Very helpful thanks. Curly braces are half my battle, I think. Is there any
way to replace the hard-coded values in the array with cell references?

Many thanks.

"Ron Coderre" wrote:

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

There sure is. Just put the table of values in a convenient place and refer
to it:

Example:
=VLOOKUP(A1,$D$1:$E$4,2,1)

Or, if you want to get a little fancier....name the range something like
LU_Rates.
(Select the range, then type LU_Rates in the Names box in the upper left of
the Excel window and press the [Enter] key)

Then you can refer to that range name in the formula:
=VLOOKUP(A1,LU_Rates,2,1)

Does that help?

--
Regards,
Ron


"jw446" wrote:

Very helpful thanks. Curly braces are half my battle, I think. Is there any
way to replace the hard-coded values in the array with cell references?

Many thanks.

"Ron Coderre" wrote:

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."

Think I got it now. Thanks.

"jw446" wrote:

Very helpful thanks. Curly braces are half my battle, I think. Is there any
way to replace the hard-coded values in the array with cell references?

Many thanks.

"Ron Coderre" wrote:

Try this formula:

=VLOOKUP(A1,{0,0;10,3;21,4;31,5},2,1)

Note the curly braces, the commas, and the semicolons.
The braces define an array.
The commas separate values in a row.
The semicolons are like carriage returns and begin a new row.

Effectively, the values within the braces are the equivalent of a 2 column
range of values:
_0____0
10____3
21____4
31____5

Does that help?

--
Regards,
Ron


"jw446" wrote:

Trying to create a table that will pull a price based on a value within a
range. Trick is I'm trying not to enter the entire range into the pricelist.
Example logic is "If you're aged 10-20, then you can enter for $3. If
you're aged 21-30, then you enter for $4. If you're 31 or older, you enter
for $5."