using a table as reference in creating formula

[AJ]

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

 

[Don Guillett]

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

 

[Teethless mama]

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

 

[AJ]

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

 

[Roger Govier]

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)

 

[AJ]

Hi Roger,

I did what you said and it worked.

Many thanks,
Arianne