I have no probs using hlookup when returning say row 6, but how do you 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 desired results

many Thanks

One way:

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


In article ,
andyhofer wrote:

I have no probs using hlookup when returning say row 6, but how do you
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 desired
results

many Thanks