Using Text Cell Reference for Table Array

[geralcde]

Hello all,

I would like to ask if it is possible to type in the cell reference in a cell and use that cell for the table array?

For example

Category	Count	Date		Category	Cell Start	Cell End
apple	3	1/3/2017		apple	A1	A6
apple	4	1/4/2017		Pear	A7	A11
apple	5	1/5/2017
apple	6	1/6/2017
apple	7	1/7/2017
apple	8	1/8/2017
Pear	9	1/3/2017
Pear	10	1/4/2017
Pear	11	1/5/2017
Pear	12	1/6/2017
Pear	13	1/7/2017

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

As you can see, apple is in column A and has the cell references spanning A1:A6, while pear has the cell references spanning A7:A11. I would like to use vlookup to search for the number of apples on 1/3/2017 and i would like to use vlookup(a1,a1:A6,2,False) for apple and then similar formula for pear but at a different cell reference of A7:A11 which can be seen on the right table. As I have many categories and dates, I cannot manually input cell references one by one, but I do have the row numbers. I cannot use vlookup for A1:A11 because it will only return the value for apple and i want for each category. How can i modify my formula in the table_array function to input the A1:A6, A7:A11 etc from my right hand table?

 

[Marcelo Branco]

Welcome to Mr Excel

I think you can simply use VLOOKUP (or SUMIFS) - don't need to input manually the ranges.
Tell us the expected/desired results for Apple and Pear.

M.

 

[PaddyD]

In addition to Marcelo's suggestion you might find that pivot tables can do much of what you need. If you're not familiar / confident with them, try here:

http://www.contextures.com/

...and if you can't get it sorted post back with examples of the kind of answers you're after...