Need help with Excel IF(COUNTIF>OFFSET(INDEX(MATCH formula

[zazybang]

Hey guys, I have a sheet with 2 columns, columns A and B. On a separate sheet i have 1 column labeled A.
Column A on both sheets is for names while column B on sheet 1 has names of the person who hired the person in column A. What I have done is made a formula for Sheet 2 column A, this formula Places the persons based on the criteria as to who hired them. My issue is that a lot of people were hired by one guy. So what i need is an offset formula, that will offset the first INDEX(MATCH formula to the next person who was hired by the specific person. Current formula: =IF(COUNTIF(Sheet1!B1:B21,"Randerson"),SUBSTITUTE(A1,A1,OFFSET(INDEX(Sheet1!A1:A21,MATCH(A1,Sheet1!A7:A21,0)),1,0,1,1))) What i want to do is add a formula into the "OFFSET: rows" part that will offset the formula to the NEXT person who was HIRED BY the individual named Randerson.

 

[Marcelo Branco]

Maybe something like this

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Employee​

[/TD]
[TD]

Hired By​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Emp1​

[/TD]
[TD]

xxx​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Emp2​

[/TD]
[TD]

Randerson​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Emp3​

[/TD]
[TD]

yyyy​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Emp4​

[/TD]
[TD]

Randerson​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Emp5​

[/TD]
[TD]

xxx​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Emp6​

[/TD]
[TD]

zzz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Emp7​

[/TD]
[TD]

Randerson​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Emp8​

[/TD]
[TD]

yyyy​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Emp9​

[/TD]
[TD]

zzz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Emp10​

[/TD]
[TD]

xxx​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Emp11​

[/TD]
[TD]

xyz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Emp12​

[/TD]
[TD]

xxx​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Emp13​

[/TD]
[TD]

yyyy​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Emp14​

[/TD]
[TD]

zzz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Emp15​

[/TD]
[TD]

Randerson​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

Emp16​

[/TD]
[TD]

Randerson​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

Emp17​

[/TD]
[TD]

xyz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

Emp18​

[/TD]
[TD]

xxx​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

Emp19​

[/TD]
[TD]

zzz​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

Emp20​

[/TD]
[TD]

yyyy​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

List​

[/TD]
[TD]

Criteria​

[/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Emp2​

[/TD]
[TD]

Randerson​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Emp4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Emp7​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Emp15​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Emp16​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2
=COUNTIF(Sheet1!B:B,B2)

Array formula in A2 copied down
=IF(C$2>=ROWS(A$2:A2),INDEX(Sheet1!A$2:A$21,SMALL(IF(Sheet1!B$2:B$21=$B$2,ROW(Sheet1!A$2:A$21)-ROW(Sheet1!A$2)+1),ROWS(A$2:A2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[zazybang]

I do not understand any of that at all, your are a legend, thanks my dude!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.