+ Reply to Thread
Results 1 to 6 of 6

Searching multiple critera with multiple values possible

  1. #1
    Registered User
    Join Date
    09-30-2019
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Searching multiple critera with multiple values possible

    Does anyone know if its possible to use an Index-Match or Vlookup for the following situation:

    I am trying to create a formula that will search by the Account and Quarter and populate a table with the billable hours, however there are multiple order numbers that fall under these criteria. Any help or insight would be very much appreciated!

    Capture.PNG

    Cheers,

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,872

    Re: Searching multiple critera with multiple values possible

    Welcome to the forum!

    Looks to me like a SUMIFS situation.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    09-30-2019
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Searching multiple critera with multiple values possible

    Thank you!

    That would work for a sum of the hours but what if I have a cell with a drop down list of accounts on another sheet. Is it possible to have a table populate with multiple results in seperate rows for each order depending on the Account and Quarter? See below for example (cells were manually populated):

    Attachment 643555

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    915

    Re: Searching multiple critera with multiple values possible

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Do not try to use the Paperclip icon, as this does not work on this forum.

  5. #5
    Registered User
    Join Date
    09-30-2019
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Searching multiple critera with multiple values possible

    Thank you "CARACALLA"

    I have attached a sample workbook.

    My question is, would it be possible to have the table on the "Bill" tab populate with information from the "Data" Tab depending on which account and quarter are selected. Since there are multiple work orders that fall under the same Account and Quarter criteria, I'm not sure what formula to use. V-lookup is limited to one criteria and a Index-Match doesn't seem to work if there are multiple results with the same criteria

    Thanks in advance for your help!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    915

    Re: Searching multiple critera with multiple values possible

    Bill

    A2=IFERROR(INDEX(Data!A$2:A$25,SMALL(IF(Data!$A$2:$A$25=Bill!$B$1,IF(Data!$F$2:$F$25=Bill!$B$2,ROW(Data!A$2:A$25)-ROW(Data!A$2)+1)),ROWS(Data!$A$2:Data!A2))),"")

    control+shift+enter

    copy across and down
    Attached Files Attached Files

+ 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 (multiple critera in multiple rows and columns)
    By swma in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-01-2016, 10:02 AM
  2. Multiple Values with more than one critera.
    By TaylorF70 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-27-2013, 12:39 PM
  3. sum multiple matched critera values?
    By clthompso in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2013, 04:39 PM
  4. Sumproduct with multiple critera, and summing multiple Columns
    By shanea.kr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-09-2012, 03:12 PM
  5. Formula to allcoate values based on multiple critera
    By rpjohnson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2012, 06:33 PM
  6. Search named range for multiple matches to critera and return date & values
    By a.mack123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2011, 10:07 AM
  7. Returning Top Values From Multiple Critera
    By greyvdub in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-27-2010, 03:27 PM
  8. Searching for Multiple Values
    By Templinho in forum Excel General
    Replies: 4
    Last Post: 04-18-2007, 11:59 AM

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