I am having an issue with a complicated spreadsheet. I created a smaller example version which is attached. Although the attached shows three sets of lookups (as examples), the actual sheet would only contain the J1...K5 list. The J column is Priority and the K column are container types. Using drop downs, you can change the container priority order.
The data starts out in A8...J15. I need a formula in column G that:
1. Checks Priority 1 entry in Column K
2. Matches that to the column heading on Row 8
3. Checks for a "Y" where the Column/Row intersect
4. If "Y"is found, put the Priority 1 Entry in Column G for that row
5. If "N" is found, go to Priority 2 Entry and check for a Y. Keep going until Priority list is exhausted
6. Once a "Y" is found, stop checking - only show the first match in Column G
7. If there are no "Y"'s on the entire priority list, put "No Fit" in "G"
Column G would need to be dynamic. If the priority list order is changed, it should automatically change the calculation in each row. I did a couple example priority lists and showed the corresponding results. I am not too good with complicated formulas, I am hoping someone out there can help.