I am trying to match termed employees in column c with against a master list of all firm emoployees in column e with the following:
=IF(MATCH(C2,$E$2:$E$13691,0),"N", "Y")
The formula returns N correctly but instead of a Y I get the #N/A ref error.
Any ideas?
1 Answer
For =match() to work, the value of the cells you are referencing have to be exact. Also, the match function only provides the location of the queried term in the search array. So, if you do this right, it will return a value greater than 0, which we can use to our advantage. Your =if() function requires a logical test to work; if match returns a number, it means it has found a match in the master list. We can test that number in if and see if it is greater than 0 (which it will be); you should get "y".
Try this: =if(match(c2,$e:$e,0)>0,"y","n")
Also, another problem could lie in different entries from cols C to E. Are you using names? If yes, this is a bad practice; there are too many variables you can mess up when entering text strings. Try using ID numbers instead of names. You can then use =VLOOKUP() to directly reference and match your employee names to employee ID numbers. This will work in a Workbook across different sheets.
You could try to do string matching. But, I recommend you switch to ID numbers.
