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.


[TABLE="class: grid, width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]temp1[/TD]
[TD="width: 64"]temp2[/TD]
[TD="width: 64"]date[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]enter date[/TD]
[TD="width: 64, align: right"]4/22/16[/TD]
[TD="width: 64, align: right"]35[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]4/22/16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]9/10/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1/20/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]2/12/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3/28/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]12/21/15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.