I need a formula to choose the "best option", and then choose the "next best option" for each location. Each location has multiple options which I've ranked. The data is aligned in rows matching the location. There are too many options to use nested IFs. The data is attached. The nested IFs I used in the "Optimized" formula return that data correctly, but to exclude the options I've already used in "Optimized" from the "alternate" results requires too many IFs. The ranking system is outlined on Tab 1.
Solved by S. S. in 20 mins