+ Reply to Thread
Results 1 to 4 of 4

Mutliple Index Match criteria formula needed

  1. #1
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Mutliple Index Match criteria formula needed

    Hello Excel Forum.

    I have been trying (unsuccessfully) to develop a formula I need for one of my rate calculation worksheets which I don't think should be too terribly challenging as it's not very complex at all, but the formula I have been trying in multiple versions has not accomplished the needed outcome.

    I am trying to have a formula that will look up to criteria given a specific if statement. If cell E142 = "Y", then index the range z412:931 and match with two criteria. The first criteria is the number (age) in D142 with the range H412:H931 and the second criteria is to match the value (rating area) in I142 with the range I412:I931.

    The formula works as long as the value in I142 is RA1, but if I change it to any of the other rating areas like RA2-RA5, the formula errors.

    I need the formula to be able to drag down and to the right. I've attached a sample worksheet for guidance. I need working formula in the green highlighted cells.

    Ultimately what I need to do is match someone's smoking status, their age and the rating area they live in and given those 3 data criteria, the formula needs to return the correct rate given the rate tables provided in the excel worksheet.

    Thank you in advance for your help.

    Be Well.
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Mutliple Index Match criteria formula needed

    I thinks that it is something to do with the highlighted segment

    =IFERROR(IF($E142="Y",INDEX(Z$412:Z$931,MATCH($D142,$H$412:$H$931,0),MATCH($I142,$I$412:$I$931,0))*(1+Z$302),IF(OR($E142="",$E142="N"),INDEX(Z$412:Z$931,MATCH($D142,$H$412:$H$931,0),MATCH($I142,$I$412:$I$931,0)))),"")

    This segment evaluates to 1 for the top cell but when dragged down to the cell below, it evaluates to 105 - there are not that may columns in your range.

    Try selecting syntactically sensible segments of the formula in the formula bar and pressing F9 to see what they evaluate to.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,572

    Re: Mutliple Index Match criteria formula needed

    Is it anything like this?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Registered User
    Join Date
    09-03-2013
    Location
    Elgin, IL
    MS-Off Ver
    Excel 2010
    Posts
    90

    Re: Mutliple Index Match criteria formula needed

    unfortunately, no. That won't work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help needed with Index Match formula
    By WimpieOosthuizen in forum Excel General
    Replies: 4
    Last Post: 08-01-2014, 06:11 AM
  2. Trouble with Index/Match and/or Vlookup and mutliple column matching
    By eralford in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-03-2012, 08:32 AM
  3. [SOLVED] Index Match with mutliple criteria
    By FunctionalHippie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2012, 01:34 PM
  4. index match mutliple criteria
    By 00Able in forum Excel General
    Replies: 2
    Last Post: 03-04-2011, 10:43 PM
  5. Index and Match-search across mutliple columns
    By satkadeb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-11-2008, 03:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1