+ Reply to Thread
Results 1 to 6 of 6

Finding match based on 2 criteria

  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Finding match based on 2 criteria

    Hi All,

    I have a huge dataset and need to find a specific value. Column A list all projects (active and completed) with resource type used for that project. Not all projects used the 3 types of resources so there isn't a pattern to go by except that resources are listed after each project. Column B lists dollar value for each resource. Using the example below, I want to know what Materials cost for Project2 and the answer should be 30. Any formula suggestions?

    Col A Col B
    Project-1
    Travel $5
    Personnel $100
    Materials $25
    Project-2
    Travel $6
    Materials $30
    Project-3
    Personnel $200
    Project-4
    Materials $100


    Thanks in advance!!

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

    Re: Finding match based on 2 criteria

    Where E1 = Project-2
    E2 = Materials

    =INDEX(B1:B11,MATCH(E2,INDIRECT("A"&MATCH(E1,A1:A11,0)&":A11"),0)+MATCH(E1,A1:A11,0)-1)
    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
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Finding match based on 2 criteria

    repeated by mistake :D
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: Finding match based on 2 criteria

    Are there gaps in the second column where the new project begins (Like next to "Project 2" is the cell in the B column blank?)

    Are they definitely in order? (project 1, project 2, ... etc)

    EDIT: my mistake - thought you wanted the entire project cost for each project

  5. #5
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Re: Finding match based on 2 criteria

    Thanks daffofil11!!

    i completely forgot to use INDIRECT in my attempt at this formula.


  6. #6
    Registered User
    Join Date
    03-21-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2011 & 2010
    Posts
    52

    Re: Finding match based on 2 criteria

    @GeneralDisarray - THX for looking!

+ 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. Finding Min/Max of B:B to match criteria in A:A
    By Pilgrimpete in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2015, 06:55 AM
  2. Finding a specific value based on a few criteria
    By cgs45 in forum Excel General
    Replies: 1
    Last Post: 05-30-2014, 12:54 AM
  3. Index Match based on 2 Criteria only returning 1 match
    By stsanders22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-04-2012, 11:26 PM
  4. Excel 2007 : Finding a value based on 4 criteria
    By jws1976 in forum Excel General
    Replies: 2
    Last Post: 08-27-2011, 02:08 AM
  5. Finding a closest match with more than one criteria
    By Jayana in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-15-2011, 06:45 PM
  6. Finding MIN based on adjacent criteria
    By mmchaley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-01-2009, 11:42 PM
  7. Tough One: Finding Match Based on Criteria, Returning Value
    By christopher.sul in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-02-2005, 01:42 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