Thread: lookup multiple dates

I've inherited a rolling schedule in a spreadsheet. There is one week for every four columns (name, last contact, last meeting, next contact). The next contact column contains names that are contacted every 3 months or every 6 months. It doesn't work as it's set up, as the columns just repeat, and last time I checked, 3 months from now doesn't occur on the same date as 6 months from now.

I'd like to figure out a lookup that will, for example, pull all instances of July 1 in "next contact" and return all four columns for that row - so I see the name of the person, the last contact, last meeting, and the next contact columns. A vlookup or an hlookup would want a column number, which would be 52 different numbers, so that doesn't work. Does this even seem possible?

I've attached a sample - I hope it works. Thanks for your thoughts

Is this what you wanted (please see attached)? It is a non VBA approach using a number of functions. You can change the week number and date in Sheet2 to return the matching data from Sheet1.

Please note that if you have large amounts of data this is probably not the most efficient approach.

Holy cow! Yes, it is. How did you *do* that? Brilliant! I can't even follow that formula- which is sort of the problem, because I can't really duplicate it. But I could use it as a lookup and cut and paste the resulting data into a new schedule...which probably makes the most sense. That looks like it was a lot of work. Thank you!