Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 11th 16, 05:01 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2016
Posts: 3
Default 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

  #2   Report Post  
Old 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
Default 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
  #3   Report Post  
Old August 11th 16, 08:38 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2016
Posts: 3
Default

Quote:
Originally Posted by Claus Busch View Post
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   Report Post  
Old 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
Default 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   Report Post  
Old August 12th 16, 04:17 PM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2016
Posts: 3
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining Lookup function and Sum function Cameron Excel Worksheet Functions 2 July 13th 09 02:19 AM
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU JCC Excel Discussion (Misc queries) 5 June 26th 09 09:15 PM
Excel Data Validation/Lookup function does function correcty Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM
how to combine an IF Function with a lookup function to determine [email protected] Excel Worksheet Functions 1 December 5th 06 06:09 AM
Pivot table doing a lookup without using the lookup function? NGASGELI Excel Discussion (Misc queries) 0 August 2nd 05 05:08 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017