If function with lookup
 

I am working on a spreadsheet that looks at the year entered then applies a specific multiplier (percentage). It would be nice if instead of adding to the IF function, it is manageable now but the multiplier 2015 forward changes every year.

Currently
If less than or equal to 2014 multiplier is 0.064
If 201? equals x (2015 = .067. 2016 = .072, 2017 = .077, etc.)

My goal is not to keep building the formula but change the range as years go by, in part I don't want to spend a lot of time fixing this each year.

Is there a way to combine IF and LOOKUP
The spreadsheet layout
Column A Year - the date of the deposit affected
Column B Deposit reported
Column C Deposit allowed
Column D Ineligible amount - this amount is multiplied by the multiplier
Column E Multiplier
Column F Ineligible deposit amount to be returned

The Year column is manually entered based on that year the correct Multiplier should pull in.

My first attempt did not work out =IF(HLOOKUP(A11,L2:O2,L3:O3)=L2,L3) - L2:O3 is my Multiplier table

Any suggestions or guidance is appreciated

If function with lookup
 

Hi,

Am Thu, 11 Aug 2016 17:01:56 +0100 schrieb alphanull:

Is there a way to combine IF and LOOKUP
The spreadsheet layout
Column A Year - the date of the deposit affected
Column B Deposit reported
Column C Deposit allowed
Column D Ineligible amount - this amount is multiplied by the
multiplier
Column E Multiplier
Column F Ineligible deposit amount to be returned

try for the multiplier:
=HLOOKUP(A11,$L$2:$O$3,2,0)


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Thanks for the suggestion, this works.

The one problem I am having is, how do I deal with:
if my year is less than 2014 = .064, we have deposits that are prior to the 2014 year. All years 2014 back are 0.064 while 2015 forward change.

If function with lookup
 

Hi,

Am Thu, 11 Aug 2016 20:38:16 +0100 schrieb alphanull:

The one problem I am having is, how do I deal with:
if my year is less than 2014 = .064, we have deposits that are prior to
the 2014 year. All years 2014 back are 0.064 while 2015 forward change.

try:
=HLOOKUP(MAX(A11,2014),$L$2:$O$3,2,0)


Regards
Claus B.
--
Windows10
Office 2016

Claus,

Thanks, I was no sure on using the MAX function.