OVERALL GOAL: Basically, I have two lists and I want to do a lookup function to compare them and find the overlap between them (eventually I'll be making a ven diagram of the two lists). The numbers in the two lists have two basic components: the "letter part" and the "number part" (Here's a very simple example of two lists. List 1: IPIAB40, IPIDE5, IPICC170. List 2: IPIAB56, IPIDE8). The vlookup function I can do just fine...the difficult part is that the lookup doesn't need to be exact. I want the lookup function to allow for a margin of error of +/-15 with regards to the "number part" of each list entry. For example, say I want to see if IPIDE5 from list 1 is in list 2. Obviously, that exact sequence is not present. However, IPIDE8 is present in list 2 and that is within a margin of error of +/-15. The margin of error for IPIDE5 would look like IPIDE-10, IPIDE20. IPIDE8 falls within the range, therefore, when I do my lookup function I would like it to return, "Yes, IPIDE5 is contained within list 2."

ONE POSSIBILITY: To get the margin of error of +/-15 I was going to try to find a way to actually populate list 1 with all the values within all the ranges. For example, lets take the case of IPIDE5 again. Excel could subtract 15 and add 15 to this sequence giving IPIDE-10 and IPIDE20. Then, it could recreate list 1 where it includes all values within that range. So Excel would spit out (counting by increments of 1) IPIDE-10, IPIDE-9....IPIDE8...IPIDE19, IPIDE20. Then when I do my lookup function against list 2, IPIDE8 is now present. Excel would need to do this for every entry in list 1 (so essentially the list would become 30 times longer). I'm not sure how to do what I just described.

If anyone has any other ideas I would love to hear them. Thanks!