how to combine index, match and IF

[seraphin]

I have two lists

List 1 lists the subject IDs and the number of days that subjects stay in the hospital.
List 2 lists the subject IDs and days of subjects that have blood drawn.

I want to keep the days that subjects have their blood drawn inside the hospital but assign "0" to the days when subjects have their blood drawn outside the hospital.

See example in the attachment.

I assume a combination of INDEX, MATCH and IF may give me the answer but have failed to do it - use INDEX and MATCH to match the subject IDs, then use IF to compare the days to the criteria in list 1.


Any help?

[Sam Capricci]

try this... =IF(VLOOKUP(F2,$B$2:$C$311,2,FALSE)>=G2,G2,0) in H2 dragged down.

[seraphin]

Awesome !!