# Search in dynamic range

#### kdew

##### New Member
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.

I have also posted this thread on Excelforum: How to search within a dynamic range

#### Attachments

• example.png
37 KB · Views: 6
Last edited by a moderator:

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### KRice

##### Well-known Member
Welcome to the forum.

Have a look at this and see if it will work. Inputs are in B27 and down, and the corresponding person, the number of entries to find, and the date/outcome combinations spill over into the columns to the right. If this is close, then the range references will need to be modified to point to the correct files, worksheets, and ranges in your setup. This is configured to find up to three entries in any given "person" block. If more than three entries (i.e., positives + negatives) are present, then simply copy the last two columns (the Date and Test Outcome results table) and paste them immediately to the right to create new receiver cells for a 4th entry, and so on. This approach finds the data matching the criteria you've described, but it does not carry over the cell fill color...I believe VBA would be needed to do that efficiently.
Example Excel file.xlsx
ABCDEFGHIJ
1
2File 1File 2
3
4Sample identifierDateTest outcomePersonDateSample identifierTest outcome
51610/17/2019 3:30NEGATIVEA12/13/2019 7:4518POSITIVE
6200A10/14/2019 4:0010-
72110/18/2019 4:30POSITIVEA11/14/2019 7:107-
80A10/15/2019 3:5519-
913A11/15/2019 7:209NEGATIVE
1015B10/16/2019 4:058-
11300B10/17/2019 3:304NEGATIVE
12400B12/17/2019 7:5016-
132C10/18/2019 4:301POSITIVE
14500C10/19/2019 4:3021-
1511D11/18/2019 8:205POSITIVE
16600E10/19/2019 3:3513-
173E10/20/2019 3:3515-
186F11/20/2019 7:2512-
19700F12/20/2019 7:502-
2016F10/21/2019 3:2511-
21800F10/22/2019 3:003-
2219F11/22/2019 9:4017NEGATIVE
2310G10/23/2019 4:1514POSITIVE
241000H12/23/2019 7:456-
25
26Sample IDPersonCountDateTest OutcomeDateTest OutcomeDateTest Outcome
271C110/18/19 4:30POSITIVE
282F111/22/19 9:40NEGATIVE
293F111/22/19 9:40NEGATIVE
304B110/17/19 3:30NEGATIVE
315D111/18/19 8:20POSITIVE
326H0no matchno match
337A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE
348B110/17/19 3:30NEGATIVE
359A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE
3610A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE
3711F111/22/19 9:40NEGATIVE
3812F111/22/19 9:40NEGATIVE
Cell Formulas
RangeFormula
C27:C38C27=IFERROR(INDEX(\$F\$5:\$F\$24,MATCH(\$B27,\$H\$5:\$H\$24,0)),"no match")
D27:D38D27=COUNTIFS(\$F\$5:\$F\$24,C27,\$I\$5:\$I\$24,"<>-")
E27:E38E27=IF(\$D27=0,"no match",INDEX(\$G\$5:\$G\$24,AGGREGATE(15,6,(ROW(\$G\$5:\$G\$24)-ROW(\$G\$4))/((\$F\$5:\$F\$24=\$C27)*(\$I\$5:\$I\$24<>"-")),INT((COLUMNS(\$E\$26:E\$26)-1)/2)+1)))
F27:F38F27=IF(\$D27=0,"no match",INDEX(\$I\$5:\$I\$24,AGGREGATE(15,6,(ROW(\$G\$5:\$G\$24)-ROW(\$G\$4))/((\$F\$5:\$F\$24=\$C27)*(\$I\$5:\$I\$24<>"-")),INT((COLUMNS(\$E\$26:F\$26)-1)/2)+1)))
G27:G38,I27:I38G27=IFERROR(INDEX(\$G\$5:\$G\$24,AGGREGATE(15,6,(ROW(\$G\$5:\$G\$24)-ROW(\$G\$4))/((\$F\$5:\$F\$24=\$C27)*(\$I\$5:\$I\$24<>"-")),INT((COLUMNS(\$E\$26:G\$26)-1)/2)+1)),"")
H27:H38,J27:J38H27=IFERROR(INDEX(\$I\$5:\$I\$24,AGGREGATE(15,6,(ROW(\$G\$5:\$G\$24)-ROW(\$G\$4))/((\$F\$5:\$F\$24=\$C27)*(\$I\$5:\$I\$24<>"-")),INT((COLUMNS(\$E\$26:H\$26)-1)/2)+1)),"")

#### kdew

##### New Member
Hi KRice. Many thanks for your reply. Yes, this effectively solves the problem.
Thanks a lot!

#### KRice

##### Well-known Member
Great news! You're welcome...and I appreciate the feedback.

Replies
12
Views
150
Replies
11
Views
76
Replies
1
Views
87
Replies
1
Views
54
Replies
8
Views
155

1,130,425
Messages
5,642,042
Members
417,251
Latest member
Dordrecht

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back