+ Reply to Thread
Results 1 to 11 of 11

Getting result if any one condition match

  1. #1
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    76

    Getting result if any one condition match

    Hi All,

    Thanks in advance,

    we have attached a sheet in that we want to if in column C,F,I,L have any blank cell then it will show column "A" value, we are getting the value with "if" formula and also getting the value with "Arrayformula" but if we use IF function then we need to drag and if use Arrayformula then we cant use that column because Arrayformula will show the error

    We want only the value if condition meet and next sheet, basically we are creating a dashboard, no drag and no arrayformula
    because we need to add and use other details also here

    We have given a sample data only and we have large data, i mean we have column till "ND" and row 500

    also posted in
    https://www.mrexcel.com/board/thread...1138362/page-2
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,514

    Re: Getting result if any one condition match

    You say the results are in sheet2, but sheet2 is empty?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,514

    Re: Getting result if any one condition match

    If I understand you correctly, try this, copied down...
    =IF(COUNTA(B3:M3)<>COUNTA($B$2:$M$2),A3,"")

  4. #4
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    76
    Quote Originally Posted by FDibbins View Post
    You say the results are in sheet2, but sheet2 is empty?
    Sorry, i have show the result in sheet 1, but i want in sheet 2

  5. #5
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    76

    Re: Getting result if any one condition match

    just check this formula, its good for me, short & easy formula and thanks for that

    but we need to drag this for getting the complete result, we have used this formula in attached sheet in A18 but after that we need to drag it for getting the other result, i mean A19,A20,A21

    can we get it without drag and drop

    note:Arrayformula not required
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,892

    Re: Getting result if any one condition match

    You can try this macro:
    Please Login or Register  to view this content.
    Ben Van Johnson

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,514

    Re: Getting result if any one condition match

    Quote Originally Posted by Ajay45822 View Post
    just check this formula, its good for me, short & easy formula and thanks for that

    but we need to drag this for getting the complete result, we have used this formula in attached sheet in A18 but after that we need to drag it for getting the other result, i mean A19,A20,A21
    Looks to me like my suggestion is giving you exactly what you have in A10:A13. What is it not doing that you want it to do?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,514

    Re: Getting result if any one condition match

    I take it this did work for you then?

    Thanks for the rep

  9. #9
    Registered User
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    76

    Re: Getting result if any one condition match

    Thanks FDibbins, its working

  10. #10
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    421

    Re: Getting result if any one condition match

    Please try

    A2 (in sheet2)
    =IFERROR(INDEX(Sheet1!$A$1:$A$7,AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$7)*(1/IF(ISBLANK(Sheet1!$C$1:$C$7)+ISBLANK(Sheet1!$F$1:$F$7)+ISBLANK(Sheet1!$I$1:$I$7)+ISBLANK(Sheet1!$L$1 :$L$7),1,0)),ROW())),"")

    Press Ctrl-Shift-Enter for an array formula.

    Regards.
    Last edited by AliGW; 07-01-2020 at 02:06 AM. Reason: Do NOT rmov alterrnative solutions!
    My English is very poor, so please be patient >_<"

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,216

    Re: Getting result if any one condition match

    @ Menem

    Do NOT remove possible solutions - they are all helpful and useful. Thanks.
    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.

+ 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: 15
    Last Post: 06-09-2020, 10:41 AM
  2. Match from sheet 1 to sheet 2 some condition and get the match result
    By Ajay45822 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-11-2019, 11:15 AM
  3. [SOLVED] Condition Forumula if Columns have Strings and if they match to preset condition
    By rosn.dhakal in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2018, 10:57 PM
  4. [SOLVED] INDEX/MATCH more than 1 result available but only displaying the 1st result
    By MarkPr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-23-2018, 04:48 AM
  5. [SOLVED] INDEX/MATCH generates incorrect result by referencng nearest cell if match not found
    By aglawrence in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2017, 08:59 AM
  6. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  7. [SOLVED] Help for if condition formula result only either hit or miss from mulitple condition
    By breadwinner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2013, 07:29 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