Lookup against a list and apply the same formula with different outcomes based on criteria

That title is a mouthful.

I think this may be an extreme nested if, or I could have the results in a matrix and pick the right-most column with a non-blank value.

If you look at the table below:

Ok apparently the table insert on this forum is nowhere near as sophisticated as on Mr.Excel...

table.PNG


I have a list of paper names down A, a list of template sizes down B and in C I want to have the ideal type of advert to run for each template in each paper.

Here's where things get juicy - different template sizes and the frequency a paper is selected determines the type of advert to run.

Below is the ruleset:

• Each Advert should default to "UK"
• If there are two papers in a week, one should be UK and the other should be EU
• Promo sizes are 16x9 and 17x6 and must be "Promo"
• Unless there has already been a promo, in which case make it UK
• Also, there can only be one EU per week

Anyone got any ideas for this? I'm stumped on how to make EU appear only once, everything else I have down as an independent rule in a matrix and my plan is to grab the rightmost non-blank cell of each row


The post is at the following:

forum/excel-questions/1069795-heres-doozy-lookup-against-list-determine-number-same-names-then-apply-different-formula-each-instance.html

on the Mr excel forum.