Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Index & Match Formula (Excel 2003)

    Hi,

    I have an excel sheet that has the premium amount for various age groups depending on the coverage. This is a table and i need to populate the premium in the master data depending on the person's age. I tried using one of the formula that was given in an earlier posting i had done which is in similar lines. I tried tweeking the same formula, however not able to get the desired outcome. Can some look into the same and let me know where im going wrong. Attached is the excel sheet for your reference

    Thanks
    Baiju
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Index & Match Formula (Excel 2003)

    Try this in L2 and fill down:

    =IF(ISNA(MATCH(G2,'Premiums-parents'!$A$2:$A$7)),0,INDEX('Premiums-parents'!$B$2:$C$7,MATCH(G2,'Premiums-parents'!$A$2:$A$7),MATCH(K2,'Premiums-parents'!$B$1:$C$1)))

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index & Match Formula (Excel 2003)

    Try:

    =IF(ISERROR(INDEX('Premiums-parents'!$A$1:$C$7,MATCH($G2,'Premiums-parents'!$A$2:$A$7)+1,MATCH($K2,'Premiums-parents'!$B$1:$C$1,0)+1)),0,INDEX('Premiums-parents'!$A$1:$C$7,MATCH($G2,'Premiums-parents'!$A$2:$A$7)+1,MATCH($K2,'Premiums-parents'!$B$1:$C$1,0)+1))

    Your match with the Age used the "...,0) which required an exact match and most were not exact. The "-1" needs to be a "+1" since you lookup starting in A2 but use A1 as the first value in the index.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Index & Match Formula (Excel 2003)

    Thanks Steve & Hans,

    The formulas are working perfectly.

    Regards
    Baiju

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •