Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Match Formula Problem

    I have account numbers in Col A & D. Where there is an account number in Col D but this does not appear in Col A, then the formula in Col G must generate "not in Sales", otherwise "in Sales"

    It would be appreciated if someone could kindly amend my formula
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Howard,

    In G2 then copy down:

    Code:
    =IF(COUNTIF($A$2:$A$17,D2)=0,"not in Sales", "in Sales")
    HTH,
    Maud

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, Maud

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Your welcome, glad to help. If you want to stick with the match function, this will also work:

    Code:
    =IFERROR(IF(MATCH(D2,$A$2:$A$17,0),"In sales"),"Not in Sales")

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2016-08-30)

  6. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the alternative, much appreciated

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Howard/Maud

    The formulas in posts #2 and #4 will give the same answers in your particular case, with the file you posted.

    But you should be aware that these formulas are NOT equivalent in ALL cases i.e. MATCH and COUNT can behave differently. It depends on whether you are looking at text or numeric values.

    For example, in my posted file here, I stripped the text characters from your posted account codes, and then made a couple of subtle changes in column [A], to show the differences in these MATCH and COUNT formula methods.

    zeddy
    Attached Files Attached Files

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Good point zeddy. Comparing text ('3022 and "3060") to numbers (3022 and 3060) has an affect on many formulas. Howard will in this spreadsheet, like data in most spreadsheets, need to be careful to make sure the data is of the correct type.

    Maud

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Maud

    Yes I agree. Whenever you are cross-matching items, it is important to know how formulas will handle the comparison. Although both the COUNTIF and MATCH functions are by default are case-insensitive, they treat comparisons differently. As I have shown in my example, COUNT will match both text and numeric, when looking up a numeric value, whereas MATCH does not.
    I'm sure Howard is very careful with his data, but I just wanted to let others know.

    zeddy

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy


    Thanks for pointing this out.


    Howard

Posting Permissions

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