+ Reply to Thread
Results 1 to 11 of 11

Getting result if any one condition match

  1. #1
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    241

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    241
    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
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    241

    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
    12,570

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Getting result if any one condition match

    I take it this did work for you then?

    Thanks for the rep

  9. #9
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Agra
    MS-Off Ver
    Office 2010
    Posts
    241

    Re: Getting result if any one condition match

    Thanks FDibbins, its working

  10. #10
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    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!

  11. #11
    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,369

    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!
    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.

+ 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