Prices of fruit via types (i.e. apple/orange) and dates (i.e. year/quarter)

[sampahmel]

Hello,

I am looking to draw data (i.e. price of fruits) from one tab to another tab based on dates (by year or by quarter) and criteria (i.e. types of fruit).

The complication arises because the data will expand as the dates progress. For example, the prices will be updated until 2014. Therefore, I suppose there's a need to use dynamic ranging (via OFFSET(COUNTA())?).

Can you look into this in the attached spreadsheet.

Thank you.

 

[Somendra Misra]

Hi Sampahmel,

See your file.

Edit: For expanding range you can convert your data to table and used the table ref. in formulas ref.

Regards,

 

[sampahmel]

Hi Sampahmel,

See your file.

Edit: For expanding range you can convert your data to table and used the table ref. in formulas ref.

Regards,

Hi Somendra,

I really appreciate your help.

The other problem that I did not mention earlier, is that I'm using a old old PC. As such I noticed ARRAY formula slows down my spreadsheet incredibly.

If you know of another method that can avoid the use of array formula, can you show me that?

Thank you again Somendra Misra.

 

[Somendra Misra]

See the attached file using HLOOKUP function and a non-array solution in Brown Cell.

Regards,

 

[sampahmel]

Thanks Somendra Misra for solving the problem.

 

[Somendra Misra]

Welcome back anytime!!!

Regards,