Results 1 to 8 of 8

Thread: Help needed: cell should switch to "Y" or "N" on the basis of other cells

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007

    Help needed: cell should switch to "Y" or "N" on the basis of other cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,


    I am working on an excelsheet that, via the import-function of our webshop, automatically hides the items on our webshop that are not in stock with our supplier. I would combine our own export of products (columns A-H) with the inventory of the supplier (columns L-P).


    Now what I am searching for is a formula that does the following:
    If the value of G2 is found in column L, and the corresponding cell O* reads "r", cell J2 (where the code should be entered) should read "Y". If G2 is found in column L, and the corresponding cell O reads "g", cell J2 should read "N".


    *so if the value of G2 is found in cell L2, the corresponding cell O is O2


    Your help would be greatly appreciated!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    IF O2 can only contain "r" or "g", then

    =IF(L2=G2,IF(O2="r","Y","N",""))

    else use:

    =IF(L2=G2,IF(O2="r","Y",IF(O2="g","N","")))


  3. #3
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    Hi NBVC,

    thank you for your reply.
    I tried both, but the first formula won't work because of the last "", if I delete it, the result yielded is UNTRUE. The other formula also gives UNTRUE, instead of a Y or N. Am I doing something wrong?

    Also, this only works if the value of G2 is found in L2. But I would need to search the complete column L. Should I use L:L instead of L2?

    Thank you, best wishes,

    Brecht

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(COUNTIF(L:L,G2),IF(O2="r","Y","N"),"")


  5. #5
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    Hi,

    thank you! The formula indeed switches to Y if O2 contains r, to N if it does not.
    But what I need is the cell O that corresponds with cell L where the value of G2 is found.
    So, if the value of G2 is found in L25, the value of O25 should be checked. If the value of G33 is found in L3, the value of O2 should be checked.
    Is that possible?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IFERROR(IF(INDEX(O:O,MATCH(G2,L:L,0))="r","Y","N"),"")


    This will look for a match in column L to what is in G2. It will then take the corresponding value in column O and check the value there. It will return the Y or N based on that conditional check. If there is no match to G2 in column L, the cell will remain blank. If you want other than a blank, insert the string you want returned between the last set of double quotes.


  7. #7
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    33
    Articles
    0
    Excel Version
    Version 2007
    That is exactly what I meant!
    Thank you so much :-)

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You're welcome.

    Glad we finally got onto the same page


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •