reverse lookup + array

[lezawang]

Hi
I want to ask user to enter date then excel will return the average of temp1 and temp2 (reverse look up + array). My formula is this

=AVERAGE(INDEX(A2:C7,MATCH(F1,C2:C7,0),{1,2}))

(ctr+shift+enter)

but for the date 4/22/16 , excel returned 35 ! I was expecting (35+33)/2 instead. So excel is returning the first value only! Any idea why. Thank you so much.

temp1	temp2	date		enter date	4/22/16	35
35	33	4/22/16
10	35	9/10/15
9	18	1/20/17
33	27	2/12/15
8	4	3/28/15
30	22	12/21/15

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Fluff]

Try
=AVERAGE(INDEX(A2:B7,MATCH(F1,C2:C7,0),0))

 

[Scott T]

Try

=AVERAGE(INDEX(A2:B7,MATCH(F1,C2:C7,0),0))

By putting 0 in the column augment of index you tell it to return all columns, you also do not want to include the date in the array.