Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Help with MATCH function

  1. #1
    Guest

    Default

    I am trying to compare two lists of data, each containing client names.

    I want to set a function that tests to see if a name in Col B appears in Col F. So far, I've got this formula:

    =IF(MATCH(B8,$F$8:$F$186,0),"Y","N")

    It works when the exact match is found, but if no match is found it returns #N/A. Clearly my IF statement isn't working.

    Any ideas on where I'm going wrong?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,604
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-26 08:39, Anonymous wrote:
    I am trying to compare two lists of data, each containing client names.

    I want to set a function that tests to see if a name in Col B appears in Col F. So far, I've got this formula:

    =IF(MATCH(B8,$F$8:$F$186,0),"Y","N")

    It works when the exact match is found, but if no match is found it returns #N/A. Clearly my IF statement isn't working.

    Any ideas on where I'm going wrong?
    Try:

    =IF(ISNUMBER(MATCH(B8,$F$8:$F$186,0)),"Y","N")

    or simpler:

    =IF(COUNTIF($F$8:$F$186,B8),"Y","N")


  3. #3
    Guest

    Default

    Thank you - both options worked a treat!

  4. #4
    New Member
    Join Date
    Sep 2004
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default I know wat is the problem.

    =IF(MATCH(B8,$F$8:$F$186,0),"Y","N") <--------this is wrong
    =IF(ISNA(MATCH(B8,$F$8:$F$186,0), "N", "Y") <------try this....

    See wat happens is that.......when you use match function........if match doesnt not find the value......it will show #NA error.....so wat i have done is the....i use the ISNA() function that return "True" if the error is #Na else false. I hope u got the idea....go try it......i have not test this in the database.....i hope it works............if not........u can ask me again.....

    Programing is my Nick Name

Some videos you may like

User Tag List

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
  •