 # Reduce / efficiency formula

• Hello,

Because I have many formulas in a spreadsheet trying to reduce their number.
How can I reduce / efficiency this formula:

=IF(AND(B4>=1900;B4<2000);AB20;IF(AND(\$B\$4>=2000;\$B\$4<2100);AC20;IF(AND(B4>=2100;B4<2200);AC20+1;IF(AND(B4>=2200;B4<2300);AC20+2;IF(AND(B4>=2300;B4<10000);AC20+3)))))

• Re: Reduce / efficiency formula

create a lookup table and do a vlookup or match based on condition. please attach a workbook and i will look at it

• Re: Reduce / efficiency formula

I dont want to change "if" with vlookup. I want to make more flexible formula if is possibil.

• Re: Reduce / efficiency formula

=INDIRECT(LOOKUP(\$B\$4,{1900,2000},{"AB20","AC20"}))+IF(\$B\$4>=2100,LOOKUP(\$B\$4,{2100,2200,2300},{1,2,3}),0)

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Reduce / efficiency formula

=if(match(b4,{1900;2000},1)=1,ab20,ac20+match(b4,{2000,2100,2200,2300,10000},1)-1)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!