Lookup with multiple criteria and offset

Hi,

I have a case where i need to do lookup based on some criteria.

I have issues in selecting the proper range because of some constraints.

I have attached my sample excel sheet, please check that. it will be easier for me to explain based on that.

Data Sheet:

Data sheet is my repository. here there are three companies listed and some brands are listed under these companies.( you can see all the brands are repeated under the companies)

Then there are prices for those brands for some dates.

Report Sheet:

This sheet has a drop down which i need to use to select companies ( A, B or C).

There are brands listed and i need to populate the prices for those brands from the other sheet for those dates. One catch is there is a field called as date offset. We need to offset the number of days given here to fetch the price

For example: If the user has selected Company B from the drop down and brand A is sold for 10.8 dollars on 1st and if there is a Date offset of 1, which means in the Report sheet the price 10.8 should go under the date 2nd.

Please let me know if you need more details or my explanation is not clear.

Thanks,
Sasi

Hi,

Thanks for the reply.

I tried using the above formulas. But the date offset is not taken care of.
If the date offset is 1, the prices for 1st June in data sheet should be mapped to 2nd June and so on..


Please help

Nope sorry about that..

I was not clear inititally.

Yes i need to use the date offset. In business perspective it is for collection of the sales.

For some brands the collection is after a day and for a few it is 2 days

So the report sheet should say the pending collections for that day.

Hope it is clear.

please look at the attached excel sheet, i would have placed the values accordingly.

Yeah

It Works... My Bad

The sheet that i posted here was a sample, i modified your formula directly on my target sheet and had made mistake there...

Thanks tigeravatar and FDibbins