Extract a List of Values Filtered by Criteria with Sub-Arrays (I think???)

[Briescuis]

I have a sheet in a workbook with a lot of data.

If there is an "x" in column B, I want to pull certain data from the row with an x. I tried copying the formula from another site, but it is not quite returning the correct data. The formula I have is below. Any help is much appreciated!!

=IFERROR(INDEX(Affiliations!C$2:C$3000,SMALL(IF(Affiliations!$B$2:$B$3000=$A$2,ROW(Affiliations!C$2:C$3000)-ROW(Affiliations!C$2)+1),ROWS(Affiliations!C$2:Affiliations!C2))),"")

Thank you!!

 

[bschwartz]

What results are you getting that are different than what you expect? Can you post some sample data, and the expected results?

 

[Briescuis]

Column A Column B Column C Column D Column E Column F Column G Column H
100 ABC Hospital Address State Complete Email Received LOA
101 x DEF Hospital Address State Pending Left VM Agreement

I want it to find any x in column B and pull the data from that row for Column C and Column G.

The result is giving me one further down on my list with no x

Thank you!!

 

[bschwartz]

I can't tell from the sample data you posted what row your data starts in, but maybe you need to adjust the formula to begin in row 1, like such:

=IFERROR(INDEX(Affiliations!C$1:C$3000,SMALL(IF(Affiliations!$B$1:$B$3000=$A$1,ROW(Affiliations!C$1:C$3000)-ROW(Affiliations!C$1)+1),ROWS(Affiliations!C$1:Affiliations!C1))),"")