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"
=IF(COUNTIFS(TableB[Name],[@Name],TableB[Department],"Sales"),"OK","NO")
- Scott CranerVLookup()
? - Dominique