Creating a Function
I am trying to write a lookup function that will select a rate from an array
(named NIRATES), based on three crireria. What is the neatest way of writing the function, other than using nested 'IF' statements within OFFSET/VLOOKUP? My function needs to look something like; RATE1 ( IN or OUT, EE or ER, lookupdate) Lookupdate is formatted as YEAR only. The table looks is held as follows: Year 1992 1993 1994 1995 1996 OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20% OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20% IN:EE 9.00% 9.00% 10.00% 10.00% 10.00% IN:ER 10.40% 10.40% 10.20% 10.20% 10.20% |
Creating a Function
Year 1992 1993 1994 1995 1996 <=== row 1
OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20% OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20% IN:EE 9.00% 9.00% 10.00% 10.00% 10.00% IN:ER 10.40% 10.40% 10.20% 10.20% 10.20% IN <==== A8 EE <===== A9 1996 < =====A10 10.00% <=====A11 A11 formula is: =INDEX($A$1:$F$5,MATCH(A8&":"&A9,$A$1:$A$5,0),MATC H(A10,$A$1:$F$1,0)) HTH "Stu Gnu" wrote: I am trying to write a lookup function that will select a rate from an array (named NIRATES), based on three crireria. What is the neatest way of writing the function, other than using nested 'IF' statements within OFFSET/VLOOKUP? My function needs to look something like; RATE1 ( IN or OUT, EE or ER, lookupdate) Lookupdate is formatted as YEAR only. The table looks is held as follows: Year 1992 1993 1994 1995 1996 OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20% OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20% IN:EE 9.00% 9.00% 10.00% 10.00% 10.00% IN:ER 10.40% 10.40% 10.20% 10.20% 10.20% |
Creating a Function
Use MATCH to find the year (and thus the column), MATCH to find which
row of the 4, and both MATCH functions are contained within an INDEX function which covers your rates. Hope this helps. Pete On Aug 30, 11:20 am, Stu Gnu wrote: I am trying to write a lookup function that will select a rate from an array (named NIRATES), based on three crireria. What is the neatest way of writing the function, other than using nested 'IF' statements within OFFSET/VLOOKUP? My function needs to look something like; RATE1 ( "IN" or "OUT", "EE" or "ER", lookupdate) Lookupdate is formatted as YEAR only. The table looks is held as follows: Year 1992 1993 1994 1995 1996 OUT:EE 7.00% 7.20% 8.20% 8.20% 8.20% OUT:ER 6.60% 7.40% 7.20% 7.20% 7.20% IN:EE 9.00% 9.00% 10.00% 10.00% 10.00% IN:ER 10.40% 10.40% 10.20% 10.20% 10.20% |
All times are GMT +1. The time now is 03:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com