PDA

View Full Version : Index / Match / and search for result between two dates and a little bit more complex



ashleyuk1984
01-26-2017, 01:24 PM
Hi,
Well, I didn't really know how to title this one lol.

Ok so I've been working on something today, and I'm nearly there... But I just can't work out the final part.
Basically, I have a tiny database of dates / shipping lines / and container sizes, I've completed the majority of the formula, but I don't know how to factor in a date and tell the formula to return the correct value.

I've attached a copy of the file.

So for example...

If my "Docs Received" date is: 08/11/2016
and my Shipping line is: "HANJIN - 40 FT"

Then the result would be.... £8.00 and this is correct in the formula... (but only because it's not taking into account the dates - and the index match returns the first correct value in the list).

https://3-t.imgbox.com/FtzZ51c9.jpg


But if my "Docs Received" date is: 22/11/2016
and my Shipping line is: "MAERSK - 45 FT"

Then the result should be.... £73.00 ...... And this is where I'm stuck.

https://6-t.imgbox.com/0jYxlF1L.jpg (http://imgbox.com/0jYxlF1L)

Basically I need to factor in the Documents Received date into the formula so that it looks at the correct table.
Can anybody help me with this please.

I really hope I've explained this well enough.

Thank You

18158

Leith Ross
01-26-2017, 02:34 PM
Hello ashelyuk1984,

This formula worked for me...

=IF(RIGHT(Q6,5)="40 FT",SUMPRODUCT(--(A6:A32=P6),--(C6:C32&" - "&$D$5=Q6),D6:D32),SUMPRODUCT(--(A6:A32=P6),--(C6:C32&" - "&$E$5=Q6),E6:E32)

ashleyuk1984
01-26-2017, 02:49 PM
Hi,
Thanks for taking a look.

Your formula works if the dates at 08/11/2016 & 11/11/2016... But not if they are in between... Sorry, maybe I should have been clearer on that part.

So, "Date From" to "Date To" ... 08/11/2016 to 10/11/2016

I'd like 08/11/2016 / 09/11/2016 / 10/11/2016 to take from the top block of values...
and 11/11/2016 / 12/11/2016 etc etc onwards, all the way up to 26/11/2016 to take from the bottom block of values.

Hope that is clearer.
Thank You.