Link to home
Start Free TrialLog in
Avatar of netwrked
netwrked

asked on

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.

Thanks!

Joe
Container.xlsx
Avatar of david251
david251

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.

-David251
Container.xlsx
Avatar of netwrked

ASKER

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.

Joe
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,

Joe
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That works perfectly, I truly appreciate the effort.

Joe