2 column lookup - match to date range

  • Thread starter Thread starter abehart
  • Start date Start date
A

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 :confused:
 
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
 
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
 
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)
 
Back
Top