Compare a row of cells with a column

[Xenthys]

Hi everybody,

I have a very simple question that a research couldn't answer and I suspect the solution relates to matricial formulas, which I am not a frequent user.

I have a long list of employees and their managing reporting lines all the way up to the CEO (their manager, their manager's manager, etc ...). I need to cluster those employees according to the customers their serve (A, B, C) and to do this, my idea was to look the highest manager that represents that group before the manager can no longer be segmented into a single group.

You'll find attached the sample that I put together to simplify the problem.

What I want is to compare the row of managers to the list in columns J and K to know whether one of the managers in the row serves Customer A or B which would ensure that the managee at the bottom also serves the same customer. Basically I want to compare each row's 5 cells (C to G) with each value of column K5:K8 and if one the values in the row is equal to one of the values in the column, then have a Yes or a Customer A (the formula should give automatically the answers that are in the highlighted cells)

If anything is not clear, please feel free to ask, and thank you so much in advance for your help.

Xenthys

[XLent]

one option, assuming Ken should be Customer B rather than A (June assigned to B)

Please Login or Register  to view this content.

[Xenthys]

Yes, that is exactly it, thank you so much XLent (and you are right, Ken should be Customer B, sorry for the typo!)