Hlookup, how di I get it to return more than one cell

[andyhofer]

I have no probs using hlookup when returning say row 6, but how do yo
get a hlookup to return the value of more than one row.

Example
row 1 = oranges
row 6 = 10
row 7 = 11
row 8 = 50
row 9 = 12

Function that I now how to use
=hlookup(oranges,range!A1:A8,6)
This would return the answer 10

But I want to return the total of range 6 to 8 giving an answer 71

What do I replace the 6 with in the function above to get the desire
results

many Thank

 

[JE McGimpsey]

One way:

=SUM(OFFSET(A1,5,MATCH("oranges",A1:H1,FALSE)-1,3,1))