Need help with formulas in Excel 200 spreadsheet

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.


Who is Participating?
barry houdiniConnect With a Mentor Commented:
Hello Joe,
Try this revison of david251's formula in G9
confirmed with CTRL+SHIFT+ENTER and copied down
See attached sheet where I re-ordered K1:K5 to be in the order of your original "green" example, so the results in G9:G15 match the sample results you show in G29:G35
Note: formula refers to K$5 (in bold). That would always be the last cell of any range should you change the setup......
regards, barry

Have a look at the attached file.

I created range names for each of the colors.

Select a color in cell a7, and b7:f7 will update the priority and the g9:g15 will update

I hope this helps, let me know if you need anything further.

netwrkedAuthor Commented:
Wow, that was fast.  I had never heard of Index or Indirect.  Is there any way to do it without the row 7?  On the actual sheet, there are no colors, only J1:K5.  I only used the other colors as examples to show various outputs based on various prorities.  The actual sheet would only check column K, no colors or other lookup columns.

I really appreciate the fix, this is incredibly close.

netwrkedAuthor Commented:
Just noticed one other thing, the formula on Row 14 is different then the rest.  There are thousands of rows in the actual sheet and "No Fit" is a possibility on any of them.  Is it possible to include the No Fit check into all formulas and not lose functionality of the other formulas?

Thanks again,

netwrkedAuthor Commented:
That works perfectly, I truly appreciate the effort.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.