+ Reply to Thread
Results 1 to 15 of 15

IF formula with multiple conditions using INDEX/MATCH

  1. #1
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    IF formula with multiple conditions using INDEX/MATCH

    I am looking to shorten/simplify a formula that looks up values in a table using INDEX/MATCH as part of an IF formula that includes logical OR and AND functions. For example, I could use the following general structure:

    Please Login or Register  to view this content.
    The INDEX(MATCH) elements have been abbreviated here simply to convey the general idea, but the formula can quickly run into many lines when written normally and when the number of conditions is more than 2 or 3 and there are additional nested IF statements.

    I have tried to shorten the formula using the following (ie grouping the conditional values in an OR function) but it returns a #VALUE error:

    Please Login or Register  to view this content.
    Is there another way to write a shortened conditional formula of this kind? Or does the INDEX(MATCH) need to be included for each separate condition as written in the first code snippet?
    Last edited by cad1llac; 01-30-2013 at 06:36 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: IF formula with multiple conditions using INDEX/MATCH

    Instead of posting your formula and asking us to shorten it, Can you please explain what you are trying to do with the posted formula?

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: IF formula with multiple conditions using INDEX/MATCH

    ..Or even better to upload a sample workbook?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    No problem. I thought it might have been easier to deal with in a more abstract way.

    I created a sample worksheet but the File Upload Manager has beaten me - I'm unable to upload it so will reproduce it below.

    As in the tables below, the actual workbook has a number of named tables, including:

    - Tracker: Tracks properties (eg status) about sites number 1 to n. Sample data:

    A B
    1 Site Status
    2 1 OK
    3 2 Pending
    4 3 Poor
    5 4 Very poor
    6 5 Good
    7 6 Assess

    - Events: Tracks events that are recorded in relation the various sites (eg whether an inspection has been completed at a given site).

    D E F
    1 Site Date Event
    2 1 01/01/2013 Inspection
    3 2 03/01/2013 Inspection
    4 3 01/01/2013 Inspection
    5 5 00/01/1900 Inspection

    - Audit: Uses conditional statements to determine if events have been added/recorded correctly in the Events table based on the values of certain columns in the Tracker table. For example, if a site has a status of OK, Good, Poor or Very Poor, it means an inspection has been carried out and so a corresponding entry should be included in the Events table. The Audit identifies those sites for which Event records have not been, but should be added.

    H I
    1 Site Inspection
    2 1 Completed
    3 2 Completed
    4 3 Completed
    5 4 Required
    6 5 Pending
    7 6 Not required

    The formula I used in the Inspection column of the Audit table is:

    Please Login or Register  to view this content.
    My original question was aimed at understanding if it is possible to write the relevant formula in a way that does not require repetition of the INDEX(MATCH) function for each separate condition (ie "OK", "Good", "Poor", "Very poor").

    The other alternative I tried was to group the conditional values in an OR function - eg IF(INDEX(MATCH)=OR("OK","Good","Poor","Very poor"),...

    Hope this explains what I am trying to achieve.
    Last edited by cad1llac; 01-31-2013 at 05:42 AM. Reason: Formatted tables.

  5. #5
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    bump......

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: IF formula with multiple conditions using INDEX/MATCH

    Please attach a sample workbook instead of doing copy paste of the data

  7. #7
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    As I mentioned previously, I create a sample workbook and would like to upload it but have tried both Firefox and IE and am not able to upload my sample workbook.

    That is why I set out a few sample tables.

    Not trying to be difficult. Would like to upload if I could, but I can't seem to make it work.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: IF formula with multiple conditions using INDEX/MATCH

    how big is the file you are trying to upload?

    If it is too big, try removing any info that is not directly related to your question, and if the info needed is from a formula, value the answers. as a last attempt, try and compress/zip the file
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    Quote Originally Posted by :) Sixthsense :) View Post
    Instead of posting your formula and asking us to shorten it, Can you please explain what you are trying to do with the posted formula?
    I have created a sample workbook but am unable to upload it.

    I have then set out 3 brief tables and explained what I am trying to do with the posted formula.

    Essentially what I am curious about is whether there is a way to group multiple INDEX(MATCH) functions that lookup values in the same column of a table without having the repeat the same INDEX(MATCH) part of the formula.

    So instead of:

    Please Login or Register  to view this content.
    I have tried:

    Please Login or Register  to view this content.
    Instead of 3 index functions in the above example, I am trying to refine it to 1 index function and using the logical function to group the conditional values - resulting in a much shorter formula.

    While the alternative I tried results in a VALUE error, it clearly explains what I am trying to do with the formula. And I posted because I am unaware of any other way that I can construct the formula to achieve the desired result.
    Last edited by cad1llac; 01-31-2013 at 02:22 AM. Reason: Added code blocks to formulas.

  10. #10
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    Quote Originally Posted by FDibbins View Post
    how big is the file you are trying to upload?
    File size is 11kb - tiny.

    If it is too big, try removing any info that is not directly related to your question
    What is in the sample workbook is exactly what I have reproduced and explained in post #4 in this thread. I am not able to post exactly what is in my actual workbook (privacy issues), so I created a sample with a subset of the tables that exist in the actual workbook and changed the data as this is not relevant to the question.

    if the info needed is from a formula, value the answers.
    Not quite sure what you mean.

    as a last attempt, try and compress/zip the file.
    Have tried uploading a zip file. File upload manager just hangs.

  11. #11
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: IF formula with multiple conditions using INDEX/MATCH

    ?????
    Since there is a problem in attaching a file.

    AAAA.xlsx
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  12. #12
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Thumbs up Re: IF formula with multiple conditions using INDEX/MATCH

    Thank you vlady.

    I have adapted the array formula in your worksheet and have come up with a solution that works.

    As I can't upload, I will reproduce the problem/solution below for the benefit of others and as I have a couple of related questions.

    Sample tables from entry #4 in this thread are set out below:

    A B C D E F G H I J K
    1 Site Status Site Date Event Site Inspection Criteria
    2 1 OK 1 01/01/2013 Inspection 1 Completed OK
    3 2 Pending 2 03/01/2013 Inspection 2 Not required Good
    4 3 Poor 3 01/01/2013 Inspection 3 Completed Poor
    5 4 Very poor 5 00/01/1900 Inspection 4 Required Very poor
    6 5 Good 5 Pending
    7 6 Assess 6 Not required

    The array formula in cell I2:I7 is:

    Please Login or Register  to view this content.
    The 3 tables are described below:

    - Tracker (A1:B7): Tracks properties/attributes (eg in this case, just Status) about sites number 1 to n.

    - Events (D1:F5): Tracks events that are recorded in relation to particular sites (eg in this case, whether an inspection has been completed at a given site; an entry with a date = 0 means an inspection is pending, a date > 0 represents the date the inspection was completed).

    - Audit (H1:I7): Determines if events have been added/recorded correctly in the Events table as follows:

    * If the status of a site is OK, Good, Poor or Very poor (ie the criteria in K2:K5), this suggests an inspection has been completed. A corresponding entry should be included in the Events table.
    * If an entry has not yet been added, the formula returns "Required".
    * If an entry has been added, and the date = 0 (ie 00/01/1900), the formula returns "Pending".
    * If an entry has been added, and the date > 0 (ie a date is given), the formula returns "Completed".

    The Audit identifies those sites for which Event records have not been, but should be added.

    Related questions:
    - Is it possible to use table column and row names as references in the array formula?
    - If desired, is it possible to hard-code the criteria in the array formula?

  13. #13
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: IF formula with multiple conditions using INDEX/MATCH

    Your E5 is not 0
    1/0/1900 -> this is 0 -> formatted as *3/14/2001

    Related question
    are you talking about named ranges?

    Copy of times.xlsm

  14. #14
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    Quote Originally Posted by vlady View Post
    Your E5 is not 0
    1/0/1900 -> this is 0 -> formatted as *3/14/2001
    Just to clarify, I use dd/mm/yyyy date format.

    Quote Originally Posted by vlady View Post
    Related question
    are you talking about named ranges?
    I am mainly thinking about the case of converting the data ranges to excel tables and using table referencing in the array formula. I have found, for example, that I can substitute the reference Tracker[Status] in place of $B$2:$B$7 in the above formula, and similar names for the other range references in the formula as follows:

    Please Login or Register  to view this content.
    What I am still unclear about is whether I can hard-code the criteria into the array formula. For example, instead of having a separate range ($K$2:$K$5) that lists the various criteria, is it possible to somehow substitute the range $K$2:$K$5 in the formula with the actual criteria values (in this case, OK, Good, Poor, Very poor)?

  15. #15
    Registered User
    Join Date
    03-29-2010
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: IF formula with multiple conditions using INDEX/MATCH

    From my last post:

    Is it possible to somehow substitute the range $K$2:$K$5 in the formula with the actual criteria values (in this case, OK, Good, Poor, Very poor)?
    Have learned that what I was referring to is an array constant. I have tried to modify the formula to include an array constant - eg {OK;Good;Poor;Very poor} - but receive an error "The formula you typed contains an error."

    The article at Microsoft KB214286 gives a reason for this behavior and confirms this is not possible when inserting an array constant into a SUMIF(), COUNTIF(), or COUNTBLANK() function.

    The article suggests 2 alternative workarounds:

    Method 1
    To use the constants in a range reference, use a formula similar to the following:
    =SUMIF(A1:A2,2,B1:B2)

    Method 2
    To use the SUM(IF()) function to enter the array, use a formula similar to the following:
    =SUM(IF({1,2}=2,{1,1}))
    Does anyone know how I could adapt workaround method 2 for use as an alternative to the COUNTIF() function?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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