Excel - INDEX + MATCH + COUNTIF function

0
votes
  • 5 columns of 10,000+ rows
  • Column A= employee name
  • Only column A is unique
  • Columns B-D are the employee's reporting hierarchy, top to bottom from left to right
  • Column B,C,D = Senior VP name, VP name, Supervisor name
  • Column E = Manager mapping (where I need help, details below)

I need to map in column E the employee's manager based on the conditions below:

  • Column E = Column B unless the count of employees mapped to that Senior VP exceeds 1,000 -- if so, then map to Column C (VP)
  • Column E = Column D if the count of employees mapped to that VP exceeds 374

Using a simpler example below, let's say we need to match column E to B unless the count of cell value in Column B exceeds 3 then match to column C. If count of cell value is column C exceeds 2 then match to column D.

see example sheet here

1
excelvbaexcel-formula

1 Answers

0
votes

Here you go.

=IF(AND(COUNTIFS($B$1:B1,B2)>=3,COUNTIFS($C$1:C1,C2)>=2),D2,IF(COUNTIFS($B$1:B1,B2)>=3,C2,B2))

enter image description here