I am trying to use the `VLOOKUP` function in excel to return a formula. I've found plenty here on SO about using `VLOOKUP` but have a slightly different question.

I am trying to get the week's average temperature (from column B) before a specified day (in column C) that can be found in column A.

Put more simply:

If date in **cell** C2 exists in **column** A, return **average from range in column** B.

So far this is the closest I've got (thanks to other QAs on SO):

```
=VLOOKUP(C2, A1:B660, AVERAGE(B124:B130), FALSE)

```

However using the `AVERAGE` function in place of column number (which would usually be the argument used) does not seem to work.

I've attached a very simplified picture of my data:

![Data](https://i.stack.imgur.com/DXFvy.png)

If `VLOOKUP` is the wrong function to handle this what would be a better approach?
I'm running Excel 15.32 on a Mac.

**EDIT:**

**Dummy data:
<https://www.dropbox.com/s/rblk943k2zf71tw/dummy%20data.xlsx?dl=0>**