How to Automatically Get Next Destinations

[BOBINIHI]

Good day all,

I have some destinations that I want my formula to choose from until the monthly target is met.
For example , in the attached file, A has done one trip but target has not been met, which means
that my formula will need to pick options from the destinations which can be either 2,3,4,5 or 6 as the case may be.

Monthly Target Current Cost 1ST Target 2nd Target 3rd Target 4th Target 5th Target 6th Target
300,000 A 95,637 City M City N City X City Z
300,000 B
300,000 C
300,000 D

Like the above , I want my formula to tell me that A will need to go to City M, City N, City X and City Z
in order to meet monthly target.


Thanks you.


.

[GeoffW283]

I'm not clear on exactly how this is to work.

(1) In your example A starts with $95,637 - because of a visit to city-O ??

(2) "A" visits cities M, N, X, Z for a cost of 95,637+32,567+54,238+56,734+25,678 = 299,976
Why could "A" not visit city-Y rather than city-Z for a total of 264,854 which also exceeds target?

[BOBINIHI]

@GeoffW283 Thanks for your response.

The city to visit can be in any order.
Actually, the target is $300,000.

[GeoffW283]

I'm still struggling to understand the problem. I asked 2 questions in post #2 to try to begin to get a bit of insight but I don't think you have answered either. In post #3 you now say that the target is $300,000 rather than $250,000 originally stated, so now I have a 3rd question:

(3) From your original example where "A" visits cities M, N, X, Z - why is this a valid solution as it totals just $299,976 which is less than your revised target of $300,000?

In general, any further info that you could provide would be helpful.

[BOBINIHI]

Thanks once again.
A first visited City O.
It is almost impossible to get exactly $300,000, so we can use the closest.

[GeoffW283]

Still struggling to understand. Let's try this:
Here's a picture of your workbook with inputs as described below.

BOBINIHI.png

Do I manually enter destinations in D2 thru I5 as shown above and then have a formula in column-C to automatically compute the current cost??

That's what I have assumed above.

Is this what you are looking for? If so then in C2 copied down to C5
=SUMPRODUCT($N$4:$N$9, --NOT(ISERROR(MATCH($M$4:$M$9, $D2:$I2, 0))))

Let me know if this is on the right track. If not then please indicate what data is manually entered and what data is to be automatically calculated and, in words, describe the calculation.

Hopefully we're getting a bit closer!