Two Criteria Range Lookup in Excel

[Awesome Wally]

I am trying to expand on the Criteria Range Lookup that Chandoo detailed at http://chandoo.org/wp/2010/06/30/range-lookup-excel/ by adding a second criteria for the row lookup. For example say I have a list of products that sell over a given time frame from 4 different stores. The sumproduct example would work great except I need to look at the date Range and the Store (criteria#2 in my attached). I usually use Index and Match ( example on attached) but don't know how to incorporate a range of dates. Any help would be greatly appreciated!

I would also like to say this site has improved my Excel skills exponentially and given many new tools at my disposal!

 

[Nebu]

Hi:

May be something like this.

Thanks

 

[Awesome Wally]

Hi:
May be something like this.
Thanks

Thanks Nebu for taking a crack at this but the formula offered doesn't allow the range number to be be equal to the end of the range (say 25 instead of 11 in our example). Is it possible to include the whole range? For adjusting this formula to the scale I would like i don't know if the lookup is dynamic and flexible enough without creating a monster formula that becomes to difficult to maintain.

Would an offset work in this case? The primary hurdle is the finding the correct row. The column count is the easy part. Any other ideas?

 

[NARAYANK991]

Hi ,

I am sure Nebu will reply , but speaking for myself , I have not understood anything.

Can you upload a sample workbook which reflects your real-life application accurately , and explain through at least one manually worked-out example what the outputs would be ?

Narayan

 

[Awesome Wally]

Hi ,

I am sure Nebu will reply , but speaking for myself , I have not understood anything.

Can you upload a sample workbook which reflects your real-life application accurately , and explain through at least one manually worked-out example what the outputs would be ?

Narayan

I was laying in bed and the answer came to me. If I can have the sumproduct look at 2 criteria (the start and the end) by using * between the arrays why not add a 3rd and * that array in too. Thanks for the quick responses as usual with this site.

For more clarity check out the attached example with the new piece added onto the sumproduct calculation. Seems so easy now after thinking about it. Thanks again all!

 

[Nebu]

Glad that you solved it....