+ Reply to Thread
Results 1 to 12 of 12

INDEX Array Formula with multiple criteria in multiple columns, is it possible?

  1. #1
    Registered User
    Join Date
    02-01-2016
    Location
    Milwaukee, WI
    MS-Off Ver
    2013
    Posts
    28

    INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Hi everyone,

    Due to the formula pulling from a large database of confidential information I can only post the formula. The two issues I am having is that I can only set one criteria (>=480) when I want a second one looking for an "ADMIT" status in column O. The second issue is that my formula keeps returning one row past the designated criteria. Thanks in advance for the assistance.

    =IF(ISERROR(INDEX('01-31-16'!$A$2:$Z$100,SMALL(IF('01-31-16'!$Y$1:$Y$100>=480,ROW('01-31-16'!$Y$1:$Y$100)),ROW(1:1)),12)),"",INDEX('01-31-16'!$A$2:$Z$100,SMALL(IF('01-31-16'!$Y$1:$Y$100>=480,ROW('01-31-16'!$Y$1:$Y$100)),ROW(1:1)),12))

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Hi jparve and welcome to the forum,

    I can't do much with just a formula. A full sample workbook is much easier to understand.

    That being said the one off problem might be because you have $A$2 in part of your formula and then go to $Y$1 in another.

    Try making your $Y$2 a $Y$1 and see if that fixes the one off problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    A couple of issues...

    What version of Excel does this have to work in? If it's Excel 2007 or later you can reduce the length of the formula by half!

    Also, you're indexing rows A2:Z100 but using rows Y1:Y100 in the criteria.

    Index the entire column like this:

    INDEX('01-31-16'!$L:$L,SMALL(IF('01-31-16'!$Y$1:$Y$100>=480,ROW('01-31-16'!$Y$1:$Y$100)),ROWS(A$1:A1)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-11-2015
    Location
    Southend
    MS-Off Ver
    2013
    Posts
    52

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    You are trying to do too much in one formula
    There are ample columns in excel try solving your problem one step at a time in multiple columns
    Also for dates and other data (eg >480 towards the end) do not hard code them set up a "Data" tab where the dates and other values are put in
    Difficult to be exact without seeing your spreadsheet but a column that explores row Y and comes up with a yes/no and another that explores column O for a yes/no, and a third that combines the answer would both be more easy to understand and a lot easier to debug
    In general use a new column for each step of the calculation (good news - they are both plentiful and free) and then debug one column at a time - I have been amazed how often I have found faults in my own spreadsheets in places I least expected by using this technique
    Last edited by Graham Griggs; 02-01-2016 at 08:19 PM.

  5. #5
    Registered User
    Join Date
    02-01-2016
    Location
    Milwaukee, WI
    MS-Off Ver
    2013
    Posts
    28

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    LOS Sample.xlsx

    Please see sample I've attached, thank you for your help.

  6. #6
    Registered User
    Join Date
    02-01-2016
    Location
    Milwaukee, WI
    MS-Off Ver
    2013
    Posts
    28

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Please just look at the "Chart Audits > 8 hrs", I changed all of the data obviously. I've edited the formula since but I am still having the problem with it returning data outside of the criteria I set. I usually get one more row than I want.

    MarvinP - I was referencing the entire array that's why I made it $A$2.

    Tony Valko - I'm using excel 2013

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    I am trying to understand what is being asked. The formula in column O of the "Chart Audits > 8 hrs" sheet is a simple countifs formula. The formulas in columns A,B,C,D,E,G and I do look like the one that you posted in your first post. Looking at the data being referenced on the 1-31-16 and 1-30-16 sheets it looks like the formulas are pulling correctly. Could you provide your expected values for some of the cells on the "Chart Audits > 8 hrs" so that we can try to ascertain what errors are being made?
    Last edited by JeteMc; 02-11-2016 at 01:13 PM.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    02-01-2016
    Location
    Milwaukee, WI
    MS-Off Ver
    2013
    Posts
    28

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    row 6 is pulling a row from the data that is not within the criteria, for example it pulled a LOS of 465 on 1/31/16 when I am looking for > 480, it always pulls data one row past the criteria. I hope that makes sense.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    I see what you are saying now and think that I have a solution. Change the array formula in Chart Adult > 8 Hrs!A2 to:
    Please Login or Register  to view this content.
    Here is a copy of your file with the modified formula applied only to the rows that pull data from 1-31-16:
    Copy of LOS Sample-2.xlsx
    Let me know if you have any questions.

  10. #10
    Registered User
    Join Date
    02-01-2016
    Location
    Milwaukee, WI
    MS-Off Ver
    2013
    Posts
    28

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Perfect, thanks. I can't believe it was just those two things. God I'm an idiot.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Not an idiot at all, after all you knew where to ask for help. At any rate You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: INDEX Array Formula with multiple criteria in multiple columns, is it possible?

    Quote Originally Posted by JeteMc View Post
    =IF(ISERROR(INDEX('01-31-16'!$A$1:$Z$100,SMALL(IF('01-31-16'!$Y$2:$Y$100>480,ROW('01-31-16'!$Y$2:$Y$100)),ROW(1:1)),10)),"",INDEX('01-31-16'!$A$1:$Z$100,SMALL(IF('01-31-16'!$Y$2:$Y$100>480,ROW('01-31-16'!$Y$2:$Y$100)),ROW(1:1)),10))
    The OP said they're using Excel 2013 so you can reduce that formula by half replacing IF(ISERROR with IFERROR.

    Instead of indexing the 2d range and specifying the column number, just index the specific column.

    Here's how I would write that formula...

    =IFERROR(INDEX('01-31-16'!J:J,SMALL(IF('01-31-16'!Y$2:Y$100>480,ROW('01-31-16'!Y$2:Y$100)),ROWS(A$1:A1))),"")

    Still array entered.

+ 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] Index Match with Multiple Criteria and Multiple Columns
    By KAVS101 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-23-2016, 06:39 AM
  2. Need an array possibly INDEX/MATCH formula with multiple lookup criteria.
    By TheClaw2323 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 09:59 AM
  3. Replies: 6
    Last Post: 10-22-2014, 09:07 AM
  4. Index Match Array Multiple Criteria
    By Keelin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-23-2014, 02:48 AM
  5. Replies: 4
    Last Post: 03-27-2014, 01:09 PM
  6. Excel 2007 : Index, Match, Large Formula: Multiple Criteria, Multiple Ranges
    By SimpleJack in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2013, 08:54 AM
  7. [SOLVED] INDEX Array Formula with multiple criteria? possible?
    By a.mack123 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-09-2012, 12:25 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