2 column lookup - match to date range

[abehart]

I have 2 spreadsheets. 1 is a list of procedures. This list has the
columns id_code, name, date_procedure

The other is a list of test results with the columns id_code,
date_test,result.

How can I use the look up functions (INDEX MATCH for eg) to match
procedures to test results only if the test preceeded the procedure by
<=14 days and >=0 days ?

Thank you for your thoughts

 

[CLR]

I would first combine both lists according to the common id_code, so all the
relative data was in one row for each id_code.....then add a column at the
end to determine the difference in the test and procedure dates......

hth
Vaya con Dios,
Chuck, CABGx3

 

[abehart]

That might be an option with smaller data sets. My results table ha
200,000 rows spread across 5 worksheets and the second list 3000 rows
Each also has 20 columns each

 

[Bob Phillips]

You could try this

=ISNUMBER(MATCH(1,(A2=procedure_ids)*(B2+14>=procedure_dates)*(B2<=procedure
_dates),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

where A2 is the test results id code, and B2 the date. So this formula goes
in C2 on that sheet.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)