+ Reply to Thread
Results 1 to 13 of 13

Return y or n based on a particular column, if multiple criteria match

  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Return y or n based on a particular column, if multiple criteria match

    Hi,

    I have attached a Sample Workbook.
    Now, it's not the easy IF and match criteria.

    I have to look in 3 different columns and match and see in column F, if it is Y, N or blank... and based on the F column, return the value.


    It's a bit complicated one! please check if someone can help me out! if not by functions then vba is also fine.
    Attached Files Attached Files
    Last edited by Kinjal Doshi; 01-09-2021 at 08:01 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,395

    Re: Return y or n based on a particular column, if multiple criteria match

    See if this works for what you want in G2 copied down:

    =IF(AND(COUNTIF(E$1:E1,E2)=COUNTA(E$1:E1),F2="Y"),"Y","N")

    If so, then you can adapt it to fulfil the two other requirements.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    Hi,

    This is not matching my requirement... I am attaching updated one which gives more clarity on what I am looking for.
    Please check if this can help. Updated Logic is highlighted in Red.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    Hi Ali,
    Could you find a solution to this?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,395

    Re: Return y or n based on a particular column, if multiple criteria match

    You put my formula in the wrong cell - it needs to go at the top of the column and be copied down.

    Please state the requirements here in a post (not just in your attachment).

  6. #6
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    Hi Ali,

    okay.. sorry about that... I tried that too.. but as per the attached screenshot, it is still giving N

    and the requirement is as below

    IF the number in current row of column E is found in all above rows (excluding current row) and if column F is Y against this number in any of above rows then return Y ELSE RETURN N. And if the number is not found in all above rows then RETURN N.... additionally, if F column has Y against any of the number (excluding current row number), then return Y and if it is blank or N then return N
    Attached Images Attached Images
    Last edited by Kinjal Doshi; 01-10-2021 at 07:44 AM.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Return y or n based on a particular column, if multiple criteria match

    Is this correct for column G. If so, I'll look at the remainder...

    =IFERROR(IF(AND(ISNUMBER(MATCH(E2,$E$1:E1,0)),INDEX($F$1:$F1,MATCH(E2,$E$1:E1,0))="Y"),"Y","N"),"N")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Return y or n based on a particular column, if multiple criteria match

    My basic uncertainty is... the formula returns a Y, as requested. What do you want it to return when more data are added and it is no longer the last row?

  9. #9
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    Hi Glenn,

    It did work for the last row 11... then I added another row 12 at the bottom and gave Y against row 10 for the same contact no.. but it is not taking Y.. it is showing N... not sure why, see below image attached.
    Attached Images Attached Images

  10. #10
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    What do you want it to return when more data are added and it is no longer the last row? -- Nothing... Let it stay as it is.
    Only current row is important for me, each time I enter new line.. I want to see if they have Y or N

    Also for Line 8, where F column has Y... it should give me Y only... whether the number was entered earlier or not... if F column is Y against that Number... then it should return me Y only

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Return y or n based on a particular column, if multiple criteria match

    I was overcomplicating things...

    In G2, copied down:
    =IF(COUNTIFS($E$1:E1,E2,$F$1:F1,"Y")>0,"Y","N")
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2016
    Posts
    38

    Re: Return y or n based on a particular column, if multiple criteria match

    Hi Glenn,

    It's perfect!...I copied the formula in rest of the two columns, and it works perfectly. thanks!

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,895

    Re: Return y or n based on a particular column, if multiple criteria match

    You're welcome and thanks for the rep.

+ 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. Replies: 3
    Last Post: 08-17-2019, 05:53 AM
  2. [SOLVED] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  3. [SOLVED] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  4. Match & return data across multiple sheets, multiple criteria
    By mrsproctor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2015, 06:01 PM
  5. MATCH using Multiple Criteria and Return the Row Numbers
    By biosonik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2013, 02:56 PM
  6. Trying to match multiple criteria and return a corresponding value
    By consulttk in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-30-2012, 04:07 PM

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