Data matching from 2 large data sets

Hey all!

I work in a science lab and I'm trying to make a spreadsheet to help with our data analysis. For each test we run we calculate about 200 theoretical values which we hope to see and when we run the experiment we will collect about 500 to 1000 data points. At that point we will usually manually look to see if any of the theoretical values match up to what we saw in the actual experiment.

So I was wondering if Excel could do this. I would need the points matched on there absolute difference (the closest observed value either higher or lower than the theoretical). Also, would there be a way to specify a range to match. For instance, if the theoretical value is 1100, could I specify to find the closest match within 5 units so it would match up to 1097 but not 1108.

Thanks in advance

I attached a simple sample spreadsheet for what I'm trying to make. Not sure if this makes any difference, but let me go into more detail on what I'm going to use it for.

I analyze proteins using mass spectrometry in the lab. We study proteins by essentially blowing them up into smaller fragments and piecing them back together based on how well their pieces match the intact protein sequence. So what we do is first use a program that gives theoretical values on how a particular protein will explode (i.e. how large the pieces will be). We then run the experiment and try to match up our observed protein pieces with the theoretical ones. If they match then that means we can confirm the identity of the protein, if they dont match then that means we are not looking at our protein of interest. Since the methods are not perfect, we allow some difference between the theoretical and actual values but only a little.

Like I said before we usually have about 200-300 theoretical values and about 1000 actual values and normally we try to match them up by hand which takes a while. So going back to my first post, I need Excel to try to see if any of the theoretical peaks match the actual values within a certain amount of error. I know how to get Excel to find a closest match but not within a certain amount of error.

Thanks for the help!

Put this in D8 of the file u posted.

=INDEX($C$8:$C$18,MATCH(MIN(INDEX(ABS(B8-$C$8:$C$18),0,0)),INDEX(ABS(B8-$C$8:$C$18),0,0),0))

Miss out one thing....

put this in E8

=IF(B8-D8<2,IF(D8-B8<2,D8,""),"")

Hope it helps.