+ Reply to Thread
Results 1 to 13 of 13

Search for text in First set e.g Apple and Min value from the corresponding set

  1. #1
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Search for text in First set e.g Apple and Min value from the corresponding set

    Below is my Query. I want to get an solution for this

    A1(Apple), B1 (Grapes), C1(Apple), D1(Apple) are my first set of columns

    E1(1.1), F1 (3.6), G1(6.8), H1(8.9) are my second set of columns

    Want to search for the text in First set e.g Apple and Min value from the corresponding Cell value in second set

    Expected output should be Min (E1,G1, H1) only Apple's min value'
    Ans: Min (1.1,6.8,8.9)= 1.1
    Please help me on this to write the condition in excel

    Corrected the cell




    EXCEL.JPG
    Last edited by AliGW; 04-12-2019 at 06:34 AM.

  2. #2
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,652

    Re: If condition function

    Hi & welcome to the board
    How about

    Excel 2013 32 bit
    A
    B
    C
    D
    1
    apple
    1.1
    apple
    2
    grapes
    3.3
    1.1
    3
    apple
    6.8
    4
    apple
    8.9
    Sheet: Sheet4

    Excel 2013 32 bit
    D
    2
    =MIN(IF(A1:A4=D1,C1:C4))
    Sheet: Sheet4
    Formula to be confirmed with Ctrl Shift Enter, not just enter

  3. #3
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Re: If condition function

    Corrected the cell. Can you please look into it now

  4. #4
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,652

    Re: If condition function

    Try

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    1
    apple grapes apple apple
    1.1
    3.3
    6.8
    8.9
    2
    apple
    1.1
    Sheet: Sheet4

    =MIN(IF(A1:D1=A2,E1:H1))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  5. #5
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Re: If condition function

    Thanks.. This makes perfect for me. I have directly given the input and derived the result in 1 cell
    =MIN(IF(A1:D1="Apple",E1:H1)).

    Thanks. I am working on the other calculations as well.. Will post if any doubts i have.

  6. #6
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    365
    Posts
    5,652

    Re: If condition function

    You're welcome & thanks for the feedback

  7. #7
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Re: If condition function

    Hi

    Can you please help me on this.

    Below are the Conditions looking for. Attached image will give you more insight

    Text should be Apple
    Options should be Either AA or BB
    If the above condtion met then add 1.1 to the Source
    Then Min (Source, (Value1, Value3)

    Excel IF Function.JPG

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,283

    Re: If condition function

    If I understand the "either...or" part correctly the following yields the expected result: =AGGREGATE(15,6,I2:L2/(A2:D2="Apple")/(E2:H2="AA")+(E2:H2="BB"),1)
    For future reference you will usually receive quicker responses if you upload a spreadsheet as opposed to a screenshot. To upload a sample spreadsheet or workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Re: If condition function

    Hi,

    Please find the below conditions am looking for. I cannot make the excelfile upload in Advance options.So attached you the image screenshot for your reference. Hope this steps and screenshot will give you what i am looking for. Thanks in advance.

    1.[Text1 - Text 4] should be only Apple
    2.Then look for AA or BB in [Option1 -Option4]
    3.If [Option1 -Option4] is having AA or BB then add (Source + 1.1) and keep the value as SourceAdd
    4.Min(SourceAdd,Value1) is Output1
    5.Min(SourceAdd,Value3) is Output3
    6.If [Option1 -Option4] is other than AA or BB (Refer Step 7)
    7.Min(Source,Value4) is Output4
    8.Output2 is ignored since it is not having Apple text

    [Output1 - Output4] is the final result fields am looking for

    Thiyag-Reply.xlsx

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,283

    Re: If condition function

    I hope that the attached copy of the file will address all the points made.
    1. The formula from post #8 is applied to A6.
    2. The following formula is applied to M3:P3 =IF(OR(E3="AA",E3="BB"),MIN($B6,I3),MIN($A6,I3))
    3. The following formula is used as a conditional formatting rule in M2:P3 =A$3<>"Apple" (font > effects > strikethrough)
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-24-2019
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    10

    Re: If condition function

    Hi JeteMc,

    Thank you for your comments on my queries. Still one more combination i am analyzing on the formula to get it.

    Please look into the attached excel and inserted comments in the output cells.

    Awaiting for your inputs and directions

    Thanks,Thiyag-Reply_1204.xlsx
    Attached Files Attached Files
    Last edited by thiyag; 04-12-2019 at 06:32 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,779

    Re: Want to search for the text in First set e.g Apple and Min value from the correspondin

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are quite new here, I have done it for you this time.)
    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.

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,283

    Re: Search for text in First set e.g Apple and Min value from the corresponding set

    I am confused as to why the O5 should be blank since C5 is Apple and G5 is AA.
    I understand why P5 should be blank and to that end this modification seems to work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that this is a regular formula. Select cell M3 and paste the formula into the formula bar and press the Enter key. Drag the fill handle over to cell P3 and then (while M3:P3 are still selected) drag the fill handle down to P5.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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