+ Reply to Thread
Results 1 to 6 of 6

Look for values based on certain criteria

  1. #1
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Arrow Look for values based on certain criteria

    My 4 input ranges are:
    D2:M2; D5:M5; D8:M9; D13:M14

    D2:M2 is either of 4 or 5 with a condition that maximum number of 5 occurrence will be one.
    D5:M5 is either 0 or 1 or 2
    D8:M9 & D13:M14 generates positive values.

    Output cells are P10 & P11

    P10=Corresponding D8:M8 analogous to the cell in which 5 is generated in D2:M2 provided ‘corresponding’ D5:M5=1
    Or
    P10=Corresponding D13:M13 analogous to the cell in which 5 is generated in D2:M2 provided ‘corresponding’ D5:M5=2

    P11=Corresponding D9:M9 analogous to the cell in which 5 is generated in D2:M2 provided ‘corresponding’ D5:M5=1
    Or
    P11=Corresponding D14:M14 analogous to the cell in which 5 is generated in D2:M2 provided ‘corresponding’ D5:M5=2

    P10 & P11=0 if D2:M2=4 (there is no 5)
    P10 & P11=0 if the ‘corresponding’ cell in D5:M5=0

    Formula required for P10 & P11
    How to accomplish? Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Look for values based on certain criteria

    =IFERROR(IF(INDEX($D$5:$M$5,MATCH(5,$D$2:$M$2,0))=1,INDEX(D8:M8,MATCH(5,$D$2:$M$2,0)),INDEX(D13:M13,MATCH(5,$D$2:$M$2,0))),0)

    it assumes 5 and 0 can not exist in your data

  3. #3
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Look for values based on certain criteria

    Thanks for the help rendered.
    Also please in P5, I require corresponding D5:M5 analogous to the cell in which 5 is generated in D2:M2. P5=0 if D2:M2=4 (there is no 5)
    Formula for P5 required

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

    Re: Look for values based on certain criteria

    Try the following in P5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    10-18-2019
    Location
    Mumbai
    MS-Off Ver
    OFFICE 2010
    Posts
    427

    Re: Look for values based on certain criteria

    Thanks JeteMc. It works.

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

    Re: Look for values based on certain criteria

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] If multiple criteria are met, then .. for all values based on 1 criteria
    By Amadan90 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-18-2021, 03:18 AM
  2. [SOLVED] Values based on multiple criteria including grouped criteria
    By adsako in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 03-21-2020, 01:24 PM
  3. Replies: 6
    Last Post: 08-21-2019, 09:10 AM
  4. VBA Macro Dividing Values Based upon Matching Criteria and Description Criteria
    By pvsvprasad in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 09-18-2016, 12:39 PM
  5. [SOLVED] using SUMIFS and adding values based on multiple criteria based on selection from the drop
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2015, 12:55 AM
  6. [SOLVED] Count values in one list and then narrow/combine values based on criteria
    By razz0807 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2013, 08:02 AM
  7. [SOLVED] How to sum values based on multiple criteria with multple possibilities for each criteria?
    By boredaxel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-05-2013, 02:26 AM

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