Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlook up or if function? (2003)

    Is there a formula that i can create, if i have two sheets with employee information, if a surname is displayed on both sheets, "yes""must be displayed if not "no"must be displayed. I have attached an example of this sheet. The formula must be in sheet one in coloum C.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlook up or if function? (2003)

    The data are a bit problematic since there are trailing spaces after some of the values. You can enter the following array formula in C2 (confirm with Ctrl+Shift+Enter instead of just Enter):
    <code>
    =ISNUMBER(MATCH(TRIM(A2&" "&B2),TRIM('Sheet 2'!$A$2:$A$6&" "&'Sheet 2'!$C$2:$C$6),0))
    </code>
    or if you really want Yes and No:
    <code>
    =IF(ISNUMBER(MATCH(TRIM(A2&" "&B2),TRIM('Sheet 2'!$A$2:$A$6&" "&'Sheet 2'!$C$2:$C$6),0)),"Yes","No")
    </code>
    Fill down as far as needed.

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlook up or if function? (2003)

    Hans, would you please exlain what you mean by "trailing spaces after some of the values". I'm following along some of these treads, trying to learn. Many thanks!

    Regards, Bil

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlook up or if function? (2003)

    Hi Bill,

    If you activate Sheet2 in the workbook attached by svokozela and select cell A4 or A5, then press the function key F2 to edit the cell contents, you'll see that there are two spaces after the letter J. There are no spaces after the letter j in cells A2:A4 on Sheet1. So in a direct comparison, you wouldn't find a match. The TRIM function deletes superfluous spaces.

  5. #5
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlook up or if function? (2003)

    Thanks you Hans.

  6. #6
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlook up or if function? (2003)

    Hi Hans,
    I have tried the formula but there are two names that appear on both sheets. I copied the formula and gives me "no" for everyone. I deleted the spaces and changed the case so that both sheets look exactly the same. Could the problem be B colunm in sheet 2?

    Regards,
    Sharon

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlook up or if function? (2003)

    As I mentioned in my first reply, you should enter the formula as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter. If you use a normal formula here, it will not return correct results.

    See the attached version.

  8. #8
    Star Lounger
    Join Date
    May 2007
    Location
    Cape Town, Western Cape, South Africa
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlook up or if function? (2003)

    Thanks so much Hans.

Posting Permissions

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