CODEWITHSUNDEEP
rexcelintervalsoverlapvlookup
crysis405
crysis405

Reputation: 1131

Excel overlaping intervals with a type of vlookup

This is a snipet of the data, of which there is a ton, with explanation of what I want to do: File

Basically I have a number of subsets (marked out by 1, 2 ... in a seperate column) of data which have intervals. I need to know if the intervals in the same two subsets overlap and if yes then I need the value (column C) which is associated with the set in columns E-G to be pasted next to the interval in column J-K that overlaps with the interval in F-G. The problem is that the interval in column F-G overlaps with multiple intervals in columns J-K.

I've been trying to solve this with
=if(or(and(x>=a,x<=b),and(a>=x,a<=y)),"Overlap","Do not overlap")

But the problem is I can't find a way to do this for multiple overlaps. If you think this can't be done in excel and know how else to do it (e.g. R) please let me know. Thank you

Upvotes: 0

Views: 870

Answers (1)

barry houdini
barry houdini

Reputation: 46361

In Excel try this formula in L4 copied down

=IFERROR(INDEX(C$4:C$100,MATCH(1,INDEX((J4<=G$4:G$100)*(K4>=F$4:F$100)*(I4=E$4:E$100),0),0)),"No overlap")

This will find the first row within each subset (if any) where the F/G interval overlaps with the current row J/K interval, if no such row exists you get "no overlap"

Upvotes: 2

Related Questions