How do I reference a range of cells using a function?

[Kurt Turley]

I am trying to use a formula where a function will return a cell reference to then use in another function of the same cell.  The formula that I am trying to use is =average(vlookup(K9,A:B,2,false):vlookup(L9,A:B,2,false))

I am trying to get excel to look up a range of dates and then give me the average of the values that are right next to those dates.

1/29/18   $500

1/30/18   $400

1/31/18   $300

2/1/18     $400

2/2/18     $300

2/5/18     $400

2/6/18     $600

2/7/18     $800

2/9/18     $300

2/10/18   $400

K9 and L9 are the dates of the range that I would be looking for.  I want to get the average on a weekly basis.  How can I do this?

[#2]

Check out the AVERAGEIFS function. Suppose your min and max dates are in F1 and F2:
=AVERAGEIFS($B$2:$B$100,$A$2:$A$100,"<="&$F$1,$A$2:$A$100,">="&$F$2)

[#3]

Thank you.  I just had to switch the lesser than/equal and greater than/equal symbols and it worked.