Excel lookup values

Microsoft Excel 010 - complete package
July 13, 2011 at 00:14:58
Specs: Windows 7
i am working on premiums and for premiums actuaries provide rates based on a combination of age and insurance tenor. i have a set of names with their ages and tenor on an excel sheet and i have the rate table, which is a two-dimensional table with the first row being the various tenors and the first column, ages. Typically i manually have to match each age with the tenor per customer to get the rate. But now i have like 600,000 excel lines to work with, there's no way i am getting the rates manually. i need help asap.

See More: Excel lookup values

Report •


#1
July 13, 2011 at 01:16:16
If I understand your post correctly, I think this is what you are looking for.

With a table that looks like this in A1:D4, the formula shown below will use the values in E1 and F1 to find the intersection of those values and, in this case, return 2c

   A    B       C       D       E     F    
1  	1	2	3       2     c
2  a	1a	2a	3a
3  b	1b	2b	3b
4  c	1c	2c	3c    

=INDEX($B$2:$D$4,MATCH(F1,$A$2:$A$4,0),MATCH(E1,$B$1:$D$1,0))

The MATCH functions return the row_num and column_num arguments for the INDEX function.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question