Using Lookups & IF queries together?

**Leo7724** · 12-28-2012, 11:00 AM

Hi,

Firstly allow me to introduce myself. I'm Leo, 25, from Leeds. I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!!

Long story short, I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:

PORT L'pool PORT HK PORT Portsmouth
1.1 13-Aug-12 06-Aug-12 30-Jul-12
1.2 10-Sep-12 03-Sep-12 27-Aug-12
2.1 03-Sep-12 27-Aug-12 20-Aug-12
2.2 01-Oct-12 24-Sep-12 17-Sep-12
3.1 24-Sep-12 17-Sep-12 10-Sep-12
3.2 22-Oct-12 15-Oct-12 08-Oct-12

Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).

However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.

Any idea how I can do this???

Thank you.

I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.

ScheduleEX.jpg

**BB1972** · 12-28-2012, 11:05 AM

Hi Leo,

Welcome to the forum. In this thread: http://www.excelforum.com/excel-form...70#post3060470 there is an example in my workbook of how an IF function can be incorporated within an INDEX/MATCH function, which would give you the result you require.

If that isn't clear, upload a sample workbook - not a picture - and we can assist further.

**Leo7724** · 12-29-2012, 08:21 AM

Hi Thanks for the reply.

Please find attached worksheet below. On the UK schedule tab I've 'coded' the delivery dates 1.1, 1.2, 2.1,2.2 etc. This is the campaign number simply split into two buys. As there is only a certain amount of campaigns, campaign one comes back round within the year again so to differentiate I coded it 1.11, 1.22.

Anyway, What I'm trying to achieve is, using two variables, to return the correct delivery date.

In the example I've uploaded on the workbook, Campaign 3 Port HK should return the values 17/sep & 15/oct in the yellow cells on the UK tab. If I were to change the port to YN for example, the dates returned would change to 24 sep / 22 oct. If I were to THEN change the campaign number to 4 (for example) the dates would change to 15 oct and 5 nov.

Hope this is clear. I'm sure this is child's play for most of you but any help would be really appreciated!

Cheers Brendan.