1. ## 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

2. Howard,

In G2 then copy down:

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

3. Thanks for the help, Maud

4. 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. Thanks for the alternative, much appreciated

7. 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

8. 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. 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. 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
•