# Which formula to use for this...

#### hercule_p2001

##### New member
Hi

I have a worksheet with a list of daily patients that I see. The details that I am interested in(from the many columns) are: Date, Patient Name, Diagnosis, Follow-up Date, Notes. These are the columns I am interested in and they are not contiguous. As can be guessed, the Date column will be in chronological order. The Follow-up date will have no chronology because some patients may be called after a week, some after 2 weeks, some after a month and some after 3 months.

What I need to do is, set up a filter in such a way that on Sheet 2 of the workbook, if I enter a date say todays' date in cell C2, the data from sheet 1 which match the follow-up dates of Sheet2!\$C\$2 must be listed in Sheet 2. This will enable me to see the possible appointments I have on any given day without going to Filter Data routine every now and then. So in a nutshell, given a date in the said cell, look up all values of column "Follow-up Date" in Sheet1 and copy the matching records to Sheet2. Is there a way to do this?

Thanks.

Staff member
Try this

#### Attachments

• XLGuru - 330 - Patient Follow-up.xlsx
10.4 KB · Views: 29

#### hercule_p2001

##### New member
Bob
That worked perfectly. Thanks a lot. Can you please explain to me how the array formula that you have written works? Thanks again. Appreciate the effort.

#### Bob Phillips

##### Super Moderator
Staff member
Okay, I'll have a go. I'll chop the range down to 2:20 instead of 2:200 to help show the details.

The main logic is a simple IF test that checks whether the follow-up dates on Patient List equal the user defined date

IF('Patient List'!\$D\$2:\$D\$20=\$C\$2,ROW('Patient List'!\$D\$2:\$D\$20))

this builds an array of matching row numbers where the dates match, or FALSE where they do not. In our exampl, with a defined date of 8th Aug, this creates an array of

{2;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The formula then takes the next smallest value from that array

SMALL(IF('Patient List'!\$D\$2:\$D\$20=\$C\$2,ROW('Patient List'!\$D\$2:\$D\$20)),ROW(A1))

We use the SMALL function as that can get the nth smallest values, MIN only gets the very smallest, and we use the variable ROW(A1) to get the 1st smallest, then the 2nd smallest, ..., because A1 increments to A2 on the second row, and so on. So this returns a value of
{2}, then
{6}, and then a series of
{FALSE}

The ISERROR checks this returned value, so if we have an error we output "", just to keep it looking good. If it is not an error, we repeat the SMALL(IF(... to return that row number. This is saved in column A with a white font to hide it.

Finally, we use a simple INDEX(range, row_num) to get the actual value, row_num being the value in column A, range being the variable column, A:A, B:B, etc.

Last edited:

#### jpr73

##### New member
Hello Bob,
You are the Super Doctor Excel Guru.
Thanks

#### hercule_p2001

##### New member
Bob

That was the best I could have hoped for. Your explanation was really better than what I tried to read in the Excel Help. I had posted this question in another Excel forum too before I found this site. And NO ONE has replied yet! I am sure to return to this forum for my excel questions. Am just going to post a new one.

Thanks.