Remember Me?

#1
August 11th 16, 05:01 PM
 Junior Member First recorded activity by ExcelBanter: Aug 2016 Posts: 3
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
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

#2
August 11th 16, 06:31 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
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
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
#3
August 11th 16, 08:38 PM
 Junior Member First recorded activity by ExcelBanter: Aug 2016 Posts: 3

Quote:
 Originally Posted by Claus Busch 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.
#4
August 12th 16, 06:56 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2011 Posts: 3,853
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
#5
August 12th 16, 04:17 PM
 Junior Member First recorded activity by ExcelBanter: Aug 2016 Posts: 3

Quote:
 Originally Posted by Claus Busch 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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Cameron Excel Worksheet Functions 2 July 13th 09 02:19 AM JCC Excel Discussion (Misc queries) 5 June 26th 09 09:15 PM Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM [email protected] Excel Worksheet Functions 1 December 5th 06 06:09 AM NGASGELI Excel Discussion (Misc queries) 0 August 2nd 05 05:08 AM

All times are GMT +1. The time now is 12:03 PM.