Excel Reference next row in array when logic is false

Question

I want tabs in my Excel sheet to reference fields in a different page if criteria is met; if the criteria is not met, I want to skip that row, and move to the next row where the criteria is met.

My database houses individuals who have been diagnosed with cancer (breast and prostate). I want the breast tracking page to only populate individuals who have been diagnosed with breast cancer (referencing the “Participant Information” sheet; column D). Currently I used an IF statement to populate this information, but my sheet populates empty rows because those rows are prostate cancer (meaning they do not meet the IF statement criteria).

I have found this formula:

{=IFERROR(INDEX('Participant Information'!A:A,SMALL(IF('Participant Information'!$D$2:$D$300="Breast",ROW('Participant Information'!$D$2:$D$300)),ROWS(1:1))),"")}

But I cannot understand how it works. It also it not working for my purpose.

TL;DR: I need to reference cells on a separate tab, without having blank rows when the criteria isn't met. I think of the "Next Record" function that is used in Mail Merge.

Answer 1

As commented above with the said functions once can accomplish desired output, here is a sample data, which reflects the outcome needed:

---

• Formula used in cell D3

=IFERROR(INDEX('Participant Information'!$B$5:$B$15,
 AGGREGATE(15,6,(ROW('Participant Information'!$B$5:$E$15)-ROW('Participant Information'!$B$5)+1)/
 ('Participant Information'!$E$5:$E$15=Records!$C$2),ROWS(Records!D$2:D2))),"")

---

• AGGREGATE() function is used to perform the aggregate calculations, here it is used for SMALL() which represents 15
• The second parameter in the above function is 6 which means to ignore error values.
• The third parameter is a array values where (ROW('Participant Information'!$B$5:$E$15)-ROW('Participant Information'!$B$5)+1) calculates the row numbers with the said range. ('Participant Information'!$E$5:$E$15=Records!$C$2) checks if the corresponding column in E matches with the criteria here it is Breast in cell C2. Next, an array is created by dividing the ROW() returns by the criteria returns to get only those which are met i.e TRUE while the others which dont meet are divided by 0 hence returns error and are ignored.
• ROWS(Records!D$2:D2) returns the smallest value,
• INDEX() function returns the name from the range based on the smallest value i.e. the row number.
• IFERROR() is used to ignore any error values down the line if there are no more retrieve any records.

---

So, that said for your sheet, it would be

=IFERROR(INDEX('Participant Information'!A$2:A$300,
  AGGREGATE(15,6,(ROW('Participant Information'!D$2:D$300)-ROW(D$2)+1)/
 ('Participant Information'!D$2:D$300="Breast"),ROWS(1:1))),"")

---

Using FILTER() it will be simple and easy as well:

=FILTER('Participant Information'!A$2:A$300,
 'Participant Information'!D$2:D$300="Breast","Not Found")

---

Sample_Data_With_Formulas