VLookup multi criteria.

Hi all,

I'm trying to save myself a mountain of time by VLooking up a multi criteria.

I've created a mountain of history for all possible outcomes for deviated mileage on a number of delivery routes (i work in transport by the way).

My history in basic consists of a number of deliveries to reoccuring customers but in different sequences.

Eg

Monday - ABCD
Tuesday - BCAD
Wednesday - CDEBA
Thursday - ACB
Friday - DCBAE

Blah blah and so forth.

The chances are that the pattern emerges again somewhere down the history but i need a quicker way of searching for it.

Is there any way i can create a search page with 5 headings "Customer 1, Customer 2, Customer 3...........etc." And create a data validation to select the customers and then that in turn searches for the same sequence of deliveries and gives me the deviated mileage specified in the last column.

Hope this makes sense.

Any help appreciated.

Hi

You should be able to do this with things like SUMPRODUCT and INDEX. Can you set up and attach an example spreadsheet that reflects your structure so we can review. From your example description, I'm not sure if the historical customer data is in 1 column, or multiple columns.

rylo

Originally Posted by rylo

Hi

You should be able to do this with things like SUMPRODUCT and INDEX. Can you set up and attach an example spreadsheet that reflects your structure so we can review. From your example description, I'm not sure if the historical customer data is in 1 column, or multiple columns.

rylo

I've attached a sample of what it looks like. I want to just select the sequence from the drop downs on the "search" Tab and then it finds the sequence on the "sequences" tab and displays the mileage in the "deviated mileage" cell.

Hope this makes sense

Originally Posted by NBVC

Are you looking for the sum of the column I in the Available sequences page, where, each delivery code matches the searches...

If so, try:

=SUM(SUMIF('Available Sequences'!A7:A9,Search!A4,'Available Sequences'!I7:I9),SUMIF('Available Sequences'!C7:C9,Search!D4,'Available Sequences'!I7:I9),SUMIF('Available Sequences'!E7:E9,Search!G4,'Available Sequences'!I7:I9),SUMIF('Available Sequences'!G7:G9,Search!J4,'Available Sequences'!I7:I9))

you would have to adjust the ranges for bigger database, and add a 5th SUMIF for the 5th sequence

Hi,

Thanks for the response.

What i'm trying to achieve is a simple way of finding what the mileage is in the last column on the "available sequences" sheet by selecting the individual delivery destinations (by store id) on the "search" tab. Normally it'd be easier to just data sort an find but the problem is i have to keep all the history in date order of occurance.

Hope this makes sense

Hi

And what you expect to happen if there isn't a matching result for your query.

rylo