2 column lookup - match to date range
 

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:


--
abehart
------------------------------------------------------------------------
abehart's Profile: http://www.excelforum.com/member.php...o&userid=33482
View this thread: http://www.excelforum.com/showthread...hreadid=532835

2 column lookup - match to date range
 

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" wrote in
message ...

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:


--
abehart
------------------------------------------------------------------------
abehart's Profile:
http://www.excelforum.com/member.php...o&userid=33482
View this thread: http://www.excelforum.com/showthread...hreadid=532835

2 column lookup - match to date range
 

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


--
abehart
------------------------------------------------------------------------
abehart's Profile: http://www.excelforum.com/member.php...o&userid=33482
View this thread: http://www.excelforum.com/showthread...hreadid=532835

2 column lookup - match to date range
 

You could try this

=ISNUMBER(MATCH(1,(A2=procedure_ids)*(B2+14=proce dure_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)

"abehart" wrote in
message ...

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


--
abehart
------------------------------------------------------------------------
abehart's Profile:
http://www.excelforum.com/member.php...o&userid=33482
View this thread: http://www.excelforum.com/showthread...hreadid=532835