Need help with formulas in Excel 200 spreadsheet

Posted on 2009-12-31
Last Modified: 2013-12-08
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.


Question by:netwrked
    LVL 19

    Expert Comment

    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.


    Author Comment

    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.


    Author Comment

    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,

    LVL 50

    Accepted Solution

    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


    Author Closing Comment

    That works perfectly, I truly appreciate the effort.


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    How to create a custom search shortcut to site-search Experts Exchange using Google in the Firefox browser. This eliminates the need to type out whenever you want to search the site. Launch your Bookmark Menu: Press 'Ctrl +…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now