Lookup Dates from two seperate data sets and return corresponding value in accending order

I've been stuck on this problem for a while now, and I'm not sure if it is something that can actually be done or not. What I need to do is lookup a value (from a data validation drop down box) in two seperate locations, and if there: return the lowest corresponding value, then the next corresponding value, then the next, etc... until there are no values left in either of the locations. The problem I am having most with this formula is the ability to search both locations at the same time and having it check back and forth for the lowest value until there are no more. Anyone know if this is possible to do? I can provide a sample if necessary.

Thanks a ton Dilipandey, thats almost exactly what I needed. One more question though, can that formula still work if the two sets of values are located in seperate worksheets? I.E. there are 3 worksheets total: one for the values that are being looked up (colums E & F), one for the first set of data B1:B8, and one for the second set of data H1:H8

Thanks, Rob

I tried that and could not get it to work. Here is what I did with the sample you provided.

I've tried tweaking the formula provided in your sample workbook to work when checking multiple worksheets, but still can't get it to work. I believe the problem is with the logic section of the IF Formula:

Formula:

Please Login or Register  to view this content.

When you highlight the logic section (ISNUMBER function) of the formula and press F9 it returns FALSE, even though there are numbers there. Subsequently in the TRUE section of the IF function when you highlight it and press F9 it returns #VALUE. Is there something that can be done with this formula to make the ISNUMBER function check multiple worksheets for the numbers. I am assuming that is the reason I'm not getting the correct results from the formula, and if its not the reason what can be done to get the desired results?

Bump No Response