Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm new to Excel and I need to use it for a scientific paper. I'm trying to optimize my database data gathering workflow.

My file!144

I need to compare the range created by the columns "Start" and "End" to the different ranges inside the yellow square and output, in the "co-localization" column, if the Start-End range overlaps just the first value (Yes - Start), just the second value (Yes - End), overlaps both (Yes - both) or is just contained (Yes - Contained). If none of the conditions is met, just output "No".

Is this too complex for Excel?


btw: I tagged this question Excel 2010, because that's what I use at home, but if you know how to do this in earlier version I would gladly accept that answer too.

share|improve this question
I would love to help, but I don't really want to put in my email address to look at the file you posted. Did you know that you can post files on Microsoft Web Apps ( for no cost? From there you can share them publicly and people don't need to login or provide info to download your file. If you post it up there you may get a few more people (including myself) to help you out. – Ben Jones Mar 4 '11 at 20:40
Done ;) I didn't know about that service. Thanks – Santiago Mar 4 '11 at 20:47
If an answer works for you, you should accept it. See FAQ – chris neilsen Mar 6 '11 at 0:08
I can't accept the answer, it says I need a rep of 15 – Santiago Mar 6 '11 at 11:02
There you go :) – Phoshi Mar 7 '11 at 9:16
up vote 1 down vote accepted

Here a solution:

First a small change to your sheet layout (values in range F2:G11)

From            To  
        0    27800000  
 34400000    46500000  
 51000000   102000000  
107000000   117600000  
120700000   153300000  
154800000   163800000  
171200000   174300000  
184000000   247200000  
999999999   999999999

Add 4intermediate result columns (i've used I, J, K, L)

add Formulas

I2    =MATCH(A2,$F$2:$F$11,1)      
J2    =MATCH(A2,$G$2:$G$11,1)+1
K2    =MATCH(B2,$F$2:$F$11,1)
L2    =MATCH(B2,$G$2:$G$11,1)+1

Copy these down for all rows in your data

Formula Co-localization Cell

D2   =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End","No"))))

To cope with the additional criteria for "Yes - Middle", add another criteria to the formula to test for spanning a from/to range

D2 =IF(AND(I2=J2,I2=K2,I2=L2),"Yes - Contained",IF(AND(I2=J2,K2=L2),"Yes - Both",IF(I2=J2,"Yes - Start",IF(K2=L2,"Yes - End",IF(AND(I2+1=J2,K2+1=L2,I2<K2),"Yes - Middle","No")))))
share|improve this answer
Thank you! I think this does it, but could you explain me why are the extra lines "0" and "999..." needed? – Santiago Mar 5 '11 at 21:41
to avoid the MATCH's returning n/a errors for 'To' < 2780000 and incorrect results From > 18400000 or To > 247200000 (i should have mentioned, the extra last value needs to be > any possible From/To value) – chris neilsen Mar 5 '11 at 22:05
@chris neilsen: Thanks for the explanation. But isn't the 999.. cell useless since your formula doesn't select it? (still works flawlessly though) – Santiago Mar 6 '11 at 9:52
btw: it's funny.. the formulas won't work in google docs (they output n/a) but work everywhere else. Just a side note, no big deal. – Santiago Mar 6 '11 at 10:50
@chris neilsen: I found a situation where your formula isn't working: the case when a sequence begins and ends outside the F2:G11 data set, but crosses it (begin>...F2:G11 data..>end). As of now this results in a "No" answer, when maybe "Yes - middle" was more appropriate. I didn't ask for this in my original request, this is a question that has risen after having put your great formula to work. How could I do this? – Santiago Mar 6 '11 at 22:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .