The following formula works:
{=IFERROR(INDEX(Data!$A:$C, SMALL(IF(Data!$A:$A = Template!$C$2, ROW(Data!$C:$C)), ROW(2:2)), 3, 1), “”)}
But when I try to add an AND statement within the IF, it breaks down:
{=IFERROR(INDEX(Data!$A:$C, SMALL(IF(Data!$A:$A = Template!$C$2 & Data!$C:$C < 100, ROW(Data!$C:$C)), ROW(1:1)), 2, 1), “”)}
Ideally, I am trying to find the other person who share any projects with Harrison, Louis S. I cant use filters because if I filter by Col A, it won’t give me the name of the other person who shares projects with such person.
Below is an example of my data.
“Template” tab/sheet
Cell C2 = Harrison, Louis S.
“Data” tab/sheet
| Person | Commission % | Project Name |
| Jane Doe | 50 | Project A |
| Harrison, Louis S. | 100 | Project B |
| Harrison, Louis S. | 50 | Project A |
| John Doe | 70 | Project C |
| Mark Doe | 30 | Project D |
| Jane Doe | 100 | Project E |
| Harrison, Louis S. | 70 | Project D |
What I want: For all Projects where the name is: “Harrison, Louis S.” and Commission % is less than 100:
“Template” tab/sheet
| Person | Column C | Column B |
| Jane Doe | 50 | Project A |
| Mark Doe | 30 | Project D |
Please let me know if I need to provide more information. I appreciate the help, thank you!