using a table as reference in creating formula
 

Hi. I need to create a formula that needs to reference a table. I can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ

using a table as reference in creating formula
 

Have a look in the help index for VLOOKUP and change your table to suit.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AJ" wrote in message
...
Hi. I need to create a formula that needs to reference a table. I can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ

using a table as reference in creating formula
 

=IF(A1="","",IF(A1<5000,100,IF(A1<6000,125,IF(A1<7 000,150,"undefined"))))
or
=IF(A1="","",LOOKUP(A1,{0,5000,6000,7000},{100,125 ,150,"undefined"}))

"AJ" wrote:

Hi. I need to create a formula that needs to reference a table. I can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ

using a table as reference in creating formula
 

Hi Teethless Mama,

Thanks for your quick reply. Since my reference table has around 30 rows,
do I just continue typing following your pattern or is there a shorter way of
doing this?

Waiting for your reply,
AJ

"Teethless mama" wrote:

=IF(A1="","",IF(A1<5000,100,IF(A1<6000,125,IF(A1<7 000,150,"undefined"))))
or
=IF(A1="","",LOOKUP(A1,{0,5000,6000,7000},{100,125 ,150,"undefined"}))

"AJ" wrote:

Hi. I need to create a formula that needs to reference a table. I can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ

using a table as reference in creating formula
 

Hi

You might find it easier to set the parameters in a table on another sheet
e.g on Sheet2 enter in columns a and B
0 100
5000 125
6000 150
and so on for the 30 or sow rows of values that you have

Then use
=VLOOKUP(A1,Sheet2!A:B,2)

--
Regards
Roger Govier



"AJ" wrote in message
...
Hi Teethless Mama,

Thanks for your quick reply. Since my reference table has around 30 rows,
do I just continue typing following your pattern or is there a shorter way
of
doing this?

Waiting for your reply,
AJ

"Teethless mama" wrote:

=IF(A1="","",IF(A1<5000,100,IF(A1<6000,125,IF(A1<7 000,150,"undefined"))))
or
=IF(A1="","",LOOKUP(A1,{0,5000,6000,7000},{100,125 ,150,"undefined"}))

"AJ" wrote:

Hi. I need to create a formula that needs to reference a table. I
can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ

using a table as reference in creating formula
 

Hi Roger,

I did what you said and it worked.

Many thanks,
Arianne



"Roger Govier" wrote:

Hi

You might find it easier to set the parameters in a table on another sheet
e.g on Sheet2 enter in columns a and B
0 100
5000 125
6000 150
and so on for the 30 or sow rows of values that you have

Then use
=VLOOKUP(A1,Sheet2!A:B,2)

--
Regards
Roger Govier



"AJ" wrote in message
...
Hi Teethless Mama,

Thanks for your quick reply. Since my reference table has around 30 rows,
do I just continue typing following your pattern or is there a shorter way
of
doing this?

Waiting for your reply,
AJ

"Teethless mama" wrote:

=IF(A1="","",IF(A1<5000,100,IF(A1<6000,125,IF(A1<7 000,150,"undefined"))))
or
=IF(A1="","",LOOKUP(A1,{0,5000,6000,7000},{100,125 ,150,"undefined"}))

"AJ" wrote:

Hi. I need to create a formula that needs to reference a table. I
can't
figure out which excel function to use or combine. As an example:

Value to check vs table = 5,250. and the table looks like this:

Range Amount
4,999.99 and below 100
5000 to 5,999.99 125
6000 to 6,999.99 150

Result should be the figures under Amount column.

Will appreciate if anyone can help me with this.

Thanks,
AJ