+ Reply to Thread
Results 1 to 6 of 6

Pull data from a table based on multiple criteria - old formula trying to use AND

  1. #1
    Registered User
    Join Date
    01-11-2016
    Location
    Savannah, Ga
    MS-Off Ver
    2016
    Posts
    17

    Pull data from a table based on multiple criteria - old formula trying to use AND

    I have an old formula I got off of this forum that I'm trying to modify to look in two different columns.

    {=IFERROR(INDEX('IP Scheme'!G:G, SMALL(IF(AND(T_IP_Scheme[Device Type]="Camera",T_IP_Scheme[Service Schedule]="Monthly"), ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}

    I added the (AND(T_IP_Scheme[Device Type]="Camera",T_IP_Scheme[Service Schedule]="Monthly") part just now and it will not return even though I think I'm using the AND statement properly. It is currently returning 0 with no errors.

    If I drop it down to only searching T_IP_Scheme[Device Type]=Camera I get a fully populated list. However we are adding our service plan to the spreadsheet and I now need to sort them by how often we will be servicing said cameras.

    Any help would be very appreciated.

    Thank you,

    Tim Weaver
    Last edited by tweaver; 05-12-2016 at 02:54 PM.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Pull data from a table based on multiple criteria - old formula trying to use AND

    Try multiplying the criteria against one another:


    {=IFERROR(INDEX('IP Scheme'!G:G, SMALL(IF((T_IP_Scheme[Device Type]="Camera")*(T_IP_Scheme[Service Schedule]="Monthly"), ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    01-11-2016
    Location
    Savannah, Ga
    MS-Off Ver
    2016
    Posts
    17

    Re: Pull data from a table based on multiple criteria - old formula trying to use AND

    That works flawlessly, why does it work that way though?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Pull data from a table based on multiple criteria - old formula trying to use AND

    The AND function is boolean, meaning it only returns a single True or False.

    Unfortunately, it doesn't retain the array of evaluations for every input. Multiplying the criteria against one another however will create a set of 1's and 0's which are interpreted by IF as Trues and Falses.

  5. #5
    Registered User
    Join Date
    01-11-2016
    Location
    Savannah, Ga
    MS-Off Ver
    2016
    Posts
    17

    Re: Pull data from a table based on multiple criteria - old formula trying to use AND

    Could this work for the first statement as well?

    {=IFERROR(INDEX((('IP Scheme'!G:G)*('Door Schedule'!Q:Q)), SMALL(IF(T_IP_Scheme[Service Schedule]="Monthly", ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}

    Now that I have the first bit working I want to make a sheet that lists all of the groups and the devices that fall within the "Monthly", "Quarterly", ect. groups

    I've tried it and its not returning anything with it like that. Curious if there is a similar fix for that part.

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    Re: Pull data from a table based on multiple criteria - old formula trying to use AND

    It's difficult to imagine how this would work without an attached example.

    If you're just trying to pull values from both columns, you could concatenate the expressions.
    {=IFERROR(INDEX((('IP Scheme'!G:G)), SMALL(IF(T_IP_Scheme[Service Schedule]="Monthly", ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")} & "," &
    {=IFERROR(INDEX((('Door Schedule'!Q:Q)), SMALL(IF(T_IP_Scheme[Service Schedule]="Monthly", ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}

+ 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] Finding a Formula to pull data from multiple columns based on todays date
    By BootleggerDave in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-18-2015, 06:56 PM
  2. [SOLVED] Help Summing Data from Table Based on Multiple Criteria
    By D. from So Cal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2014, 02:16 PM
  3. [SOLVED] Pull data from one sheet to another based on multiple criteria
    By thestalkycop in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2014, 01:38 AM
  4. Enter data into specific row in data table based on multiple criteria
    By bberger1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 11:04 AM
  5. Formula to pull rows of data based on a column cell value (Criteria)
    By PaulLor89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:26 AM
  6. [SOLVED] Pull data from multiple cells into one cell based on criteria?
    By chaddug in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-23-2012, 09:54 AM
  7. Pull from list based on multiple criteria
    By chitownsox14 in forum Excel General
    Replies: 3
    Last Post: 04-20-2011, 12:12 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