IF value is same in two columns then display a third value from another column
Results 1 to 10 of 10

Thread: IF value is same in two columns then display a third value from another column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF value is same in two columns then display a third value from another column

    Hi,

    Does anyone know which formula can achieve this:

    If value in column A is the same as value in column B, then display the value from Column C

    For example if Column A contains these values

    C1 - AAA
    C2 - BBB

    Column B contains the same:
    B1 - AAA
    B2 - BBB
    B3 - CCC

    Column C contains
    C1 - London
    C2 - New York
    Etc.

    In Column D, I would like it to insert the value from Column C, so next to "AAA", I would like it to display "London"

    Any help would be appreciated.

    Thanks in advance.

  2. #2
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Code:
    =IF(A1=B1,C1,"")
    This is the simplest of formulas... Maybe consider googling the problem before opening a new thread?
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  3. #3
    New Member
    Join Date
    Aug 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Unfortunately this doesn't seem to work when the columns are in different sheets.

    I've tried this: =IF('Sheet1'!A:A=Sheet2!G:G,Sheet2!I:I,"")

  4. #4
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Again, Google is your best friend... (although one could argue it isn't because of privacy reasons, but that's another topic)

    Code:
     =IF(Sheet1!A:A=Sheet2!G:G,Sheet2!I:I,"")
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  5. #5
    New Member
    Join Date
    Aug 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Quote Originally Posted by Tim_Excel_ View Post
    Again, Google is your best friend... (although one could argue it isn't because of privacy reasons, but that's another topic)

    Code:
     =IF(Sheet1!A:A=Sheet2!G:G,Sheet2!I:I,"")
    This doesn't seem to work. Nothing displays when I use this formula.

  6. #6
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    It does work on my end when I use regular text and number values. I'm assuming there's some information missing here. What do these ranges contain?
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  7. #7
    New Member
    Join Date
    Aug 2018
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Quote Originally Posted by Tim_Excel_ View Post
    It does work on my end when I use regular text and number values. I'm assuming there's some information missing here. What do these ranges contain?
    They contain text.

    In Sheet 1 If the value from A2 is contained in a range from another sheet (Sheet 2 Column G), then Display the corresponding Value from (Sheet 2 Column I)

    So if I have the word "Cat" in the the first sheet in Cell A2, and in sheet 2 Column G I have 30 different words and one of them is "Cat", Next to the word "Cat" in "Column I" It says "London". I would like the word "London" to display in Sheet 1 cell B2.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,063
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    seen post#7
    Last edited by Fluff; Feb 11th, 2019 at 08:09 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    That's a whole 'nother cookie

    For this you will have to use the MATCH function. This will return the row number of the found value, or #N/A if the value was not found. You can then return the value of the cell next to it. Use the IFNA function to handle values that won't be found.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: IF value is same in two columns then display a third value from another column

    Hi,

    Change/adjust cell references/range, add sheet name, as needed.

    Change "No Match" to "" (Blank) or whatever you like, formula copied down:

    ABCDEFGHI
    2CatLondonDogNew York
    3PigNo MatchMouseParis
    4ChickenNo MatchCatLondon
    5DogNew YorkHorseCalifornia
    6CowWashington

    Sheet558



    Worksheet Formulas
    CellFormula
    B2=IFERROR(LOOKUP(2,1/SEARCH(A2,G$2:G$6),I$2:I$6),"No Match")


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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