LOOKUP Having one Exact matching criteria and one between 2 date ranges.

**Grahame Hamilton** · 10-23-2012, 11:31 AM

Any help would be gratefully appreciated.

I have a set of data which 3 Columns: Client ID, Visit Date, and Status – see below

A B C D E F G
1 Client ID Visit Date Status Client ID Visit Date From Visit Date To Status
2 100 01/08/2012 100 01/08/2012 04/08/2012 Holiday
3 100 02/08/2012 100 05/08/2012 07/08/2012 Respite
4 100 03/08/2012 200 04/08/2012 06/08/2012 Holiday
5 100 04/08/2012
6 100 05/08/2012
7 100 06/08/2012
8 100 07/08/2012
9 200 01/08/2012
10 200 02/08/2012
11 200 03/08/2012
12 200 04/08/2012
13 200 05/08/2012
14 200 06/08/2012
15 200 07/08/2012

Is there a way to match exactly on columns A and E and also match between dates where column B is between Columns E and F and return the value from column G and place it in Column C where matched.

Thanks and regards Grahame Hamilton

**:) Sixthsense :)** · 10-23-2012, 11:36 AM

Please attach a sample workbook with expected output for getting quick solution.

**JosephP** · 10-23-2012, 11:41 AM

=lookup(2,1/((D:D=A2)*(E:E<=B2)*(F:F>=B2)),G:G)
adjust ranges to match your data rows

**Grahame Hamilton** · 10-23-2012, 12:12 PM

Thanks Joseph, Works a treat with a small number of rows, but struggles with alot.
I have maybe around 15,000 to 20,000 rows to calculate and it seems to sit for ages trying to calculate.
Is there another method or formula I could try?

**JosephP** · 10-23-2012, 12:47 PM

if your lookup table is-or can be-sorted by clientid we could use a longer formula but restrict the lookup to smaller ranges. a sample workbook would make it much easier to demonstrate but simply put you would use a match formula to find the first row number for the relevant id, then countif to find how many rows to use and then adjust the lookup to only search those rows (using index functions)

**Grahame Hamilton** · 10-24-2012, 06:20 AM

Hello Joseph,

Followed your advise in adjusting the data rows. so formula now reads.

=IFERROR(LOOKUP(2,1/((Diary!$D$8:$D$250=F9)*(Diary!$G$8:$G$250<=L9)*(Diary!$H$8:$H$250>=L9)),Diary!$I$8:$I$250),"")

added a little iferror tidyup! and because in reality the diary data will only hold no more than 250 rows and they start at row 8 - it now does not need to look to the bottom of the sheet!. Works a treat.

Thanks Very much Joseph. btw what does the 2,1 do?

Regards Grahame Hamilton

**JosephP** · 10-24-2012, 06:28 AM

the middle part of the function
((D:D=A2)*(E:E<=B2)*(F:F>=B2))
returns an array of 1 and 0 values (each section returns arrays of TRUE/FALSE and when multiplied they are treated as 1/0 respectively)

dividing 1 by this array returns an array of values that are either 1 or DIV/0 errors (LOOKUP ignores error values)

looking up 2 in this array matches the last instance of 1 (there should presumably only be one 1 value at most for a given set of criteria) and returns the corresponding value from column g