Originally Posted by

**nbvc**
assuming your table 1 is in sheet1, starting with headers at a1 and b1, and assuming table 2 is in sheet2, a1:b8, try in sheet1, c2:

=max(if(sheet2!$a$2:$a$8>=a2,if(sheet2!$a$2:$a$8<=b2,sheet2!$b$2:$b$8)))

adjust the sheetnames and ranges to suit, then you **must confirm this formula with ctrl+shift+enter**, not just enter, then copy down (this is an array formula)

you can replace max with min or average or almost any other similar function.

Note if you have excel 2007 or later, you can use averageifs or countifs, or sumifs instead of the array formula.

E.g.

=averageifs(sheet2!$b$2:$b$8,sheet2!$a$2:$a$8,">="&a2,sheet2!$a$2:$a$8,"<="&b2)

## Bookmarks