Results 1 to 10 of 10

Thread: Function to find specific value and return
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Function to find specific value and return

    Hi all,

    So I need help writing a formula, not in vba, that looks at a number in column A, then looks at another sheet. Finds the value from column A in a list. If a value isn't found, it returns "Check". For example:

    Sheet 1 A B C
    1 Dog
    2 Cat
    3 Frog
    Sheet 2 A B C
    1 Dog Food
    2 Cat Water
    3 Dog Nothing

    So using the tables as an example, I need a formula in sheet 1 Column B that will look at column A, find "Dog" on sheet 2. If Dog has "nothing", in column B, then retutrn the value "check".

    If that doesn't make sense I can try and describe it.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Function to find specific value and return

    What should happen if dog does have something in col b on sheet2?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Function to find specific value and return

    It should just return a blank. I would also like it to do it for cat and frog too, if that makes sense.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Function to find specific value and return

    How about
    =IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)),"",IF(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)=0,"Check",""))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Function to find specific value and return

    I am pretty Sure I got it to work with what I need. Thanks for the help Fluff!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Function to find specific value and return

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Function to find specific value and return

    What is the first part of the code doing?
    "=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)),"",...)

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Function to find specific value and return

    If the value in A2 does not exist on sheet2 you will get a #N/A error, that is just checking for that
    - 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
    Join Date
    Jun 2019
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Function to find specific value and return

    Gotcha. I'm gonna see what I can do with it. Thanks for the help.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,226
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Function to find specific value and return

    You're welcome
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •