How to search within a dynamic range

Hi all, I have the following problem to solve using Excel.

I have a list of sample numbers in one Excel file ('file 1' in attached file).

I have to couple each sample identifier from file 1 (cells in gray) to information from another sample of the same person ('file 2'). The information I need should be imported in columns C and D (which has been partially filled in for clarity).

The information from file 2 that I need concerns 1) the test outcome (column I), and 2) the test Date (column G), that belongs to the same person (column F) as the sample identifier belongs to. However, the information I need is NEVER on the same row as the gray sample identifiers, but must be extracted from columns G & I on the row where either 'Positive' or 'Negative' is in column H.

For instance, for sample 2 (and 11 and 3) I need 'Negative' from position I22 and the corresponding date in G22. For Sample 16, I need 'Negative' from I11 and date from G11.

I know how to retrieve cell content from a given relative position using INDEX and MATCH functions:
=INDEX('K:\[file1.xlsx]Tab1'!$A$1:$B$10000;MATCH(A1;'K:\file2.xlsx]Tab2'!$A$1:$A$10000;0);2)

However, the difficulty here is that I need to lookup for every sample identifier, the test outcome and date from the row where test outcome (column I) is NOT "-" for the SAME person, given in column F.

An extra difficulty would be that for person A there are multiple rows where there is not "-" in column I, and I'd need in different columns both of the test outcomes (I5 and I9) and both the dates (G5 and G9).

Any help about how to approach this is much appreciated.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Note: As you are new I will add it for you.Please read forum rules which you might have missed

https://www.mrexcel.com/board/thread...range.1132326/