0
votes

I have 2 tables, A and B table B has columns, department and name And table A has several fields, in particular the names of the employers that work for the Sales Department.

I need to create a new column in table A that checks if a name has been added in table B. The logic is if(name is matched to the names column of table B and the department= sales, then put 'OK', if not put 'NO'). The match function is fine, I am having trouble checking the value of the cell to the left of the column name i table B to check the department associated with the name. Can anyone help me? The tables are:

Table A

Name | New Column

Table B

Department | Name

I have come up with a way of using INDEX(MATCH()) to get the value of the cell next to the matched one. The issue is that a name for example can be repeated many times thoguh out table B. I need excel to look through all the matched names and check the value of the cell to the left until the value of the cell to the left = "Sales"

Use COUNTIFS(). =IF(COUNTIFS(TableB[Name],[@Name],TableB[Department],"Sales"),"OK","NO") - Scott Craner
What about VLookup()? - Dominique