How can I get <,=, or > to work with ranges

**chrish8** · 02-02-2009, 12:01 AM

Hello I am a newbie here and I don't use excel a lot but help with the following is greatly needed;

I have a small worksheet going where F25=F12+F14+15. With the value in F25 I need it to correspond with the following;
0 - 250,000 $100
250,001 - 500,000 $125
500,001 - 750,000 $325
750,001 - 1,000,000 $450
1,000,001 - 1,250,000 $490
1,250,001 - 1,500,000 $528
1,500,001 - 1,750,000 $561
1,750,001 - 2,000,000 $595
2,000,001 - 2,500,000 $634
2,500,001 - 3,000,000 $672

and return the $value into cell G25.

So if F12+F14+F15 =$40,000(and displayed in F25) I would want 100 to show in cell G25

Thanks

Chris

**vandanavai** · 02-02-2009, 04:15 AM

Hello

See formula in green coloured cell in attached file. Change value in Pink coloured cell and see result in green coloured cell.

See if this is useful to u.

**DonkeyOte** · 02-02-2009, 04:18 AM

You don't specify if your reference table is stored anywhere physically in your workbook... below is an example with the ranges stored in a static array within the formula itself...

=LOOKUP(SUM(F12,F14:F15),{0,250001,500001,750001,1000001,1250001,1500001,1750001,2000001,2500001},{100,125,325,450,490,528,561,595,634,672})

The static vectors can be stored in a range of cells... eg were:

0 to 2500001 values stored in Y1:Y10

and corresponding values:

100 to 672 stored in Z1:Z10

the above would become:

=LOOKUP(SUM(F12,F14:F15),Y1:Y10,Z1:Z10)

Obviously it's a lot easier to alter the associated values if they reside within an actual range and the formulae is a lot simpler to follow.

EDIT: royUK, Code tags used because forum software will (as advised) insert a spurious space not present within the formula itself.

**JBeaucaire** · 02-02-2009, 04:23 AM

        0	$100
  250,001	$125
  500,001	$325
  750,001	$450
1,000,001	$490
1,250,001	$528
1,500,001	$561
1,750,001	$595
2,000,001	$634
2,500,001	$672

You actually only need to list the start of each range. The next range start implicitly demonstrates where the previous range ended. So the VLOOKUP need only show:

=VLOOKUP(F25,$A$1:$B$10,2)

You can also put that lookup chart onto another sheet, perhaps a sheet where you list helper stuff and can then hide the sheet.

=VLOOKUP(F25,Sheet12!$A$1:$B$10,2)