HLOOKUP

[Guest]

I need to show the following information split into two different columns

0 55 60 62 65 70 72
55 60 62 65 70 72 75 - column X
60 62 65 70 72 - column X
62 65 70 - column X
65 - column X
70 70 72 72 75 75 - column AA
72 72 75 75 - column AA
75 75
- column AA

Example 1
If the insured age is between ages 0 and 54

column X will have 55, 60, 62, 65 and
column AA will have 70, 72, 75

Example 2
If the insured age is between ages 60 and 61

column X will have 62, 65, 70 and
column AA will have 72, 75

Can Excel condense this information to one column of numbers (rather than
the seven shown above) and provide the desires split (between columns X and
AA)?

 

[Domenic]

I'm not sure that this is what you're looking for, but try the
following...

Assumptions:

A1:G8 contains your table

W1 contains the 'Insured Age'

Formulas:

X1, copied down:

=INDEX($A$2:$G$5,ROWS($X$1:X1),MATCH($W$1,$A$1:$G$1))

AA1, copied down:

=INDEX($A$6:$G$8,ROWS($AA$1:AA1),MATCH($W$1,$A$1:$G$1))

Hope this helps!