Formula help

[GRB]

I need to write a formula that looks at these columns on one worksheet:
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=133 border=0><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" span=7 width=19><TBODY><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 18.75pt; BACKGROUND-COLOR: #f2dddc" width=133 colSpan=7 height=25>TREATMENTS NEEDED</TD></TR><TR style="HEIGHT: 49.5pt; mso-height-source: userset" height=66><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 49.5pt; BACKGROUND-COLOR: #f2dddc" width=19 height=66>NONE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>TUMBLE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>ANODIZE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>HEAT TREAT</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>TEFLON</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>PLATING</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 14pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc" width=19>OTHER</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">x</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>


and enters the appropriate treatment on another worksheet (in order):
Treatment 1 Treatment 2 Treatment 3
TUMBLE HEAT TREAT TEFLON


I want the formula to look in all the boxes between "Tumble" and "Other" to see which ones are marked and then put the appropriate treatments on Worksheet 2 under the headings "Treatment 1", "Treatment 2", "Treatment 3", etc.
For the example above, I chose 3 treatments - tumble, heat treat, and teflon. Because there is an "x" in the box on Worksheet 1, then the word "tumble" should appear under "Treatment 1" on Worksheet 2, and so on.

This is the formula I have so far, but it doesn't work:

=IF(HLOOKUP('QC Check'!$J4,'QC Check'!$J4:$O4,1,FALSE)="X","TUMBLE",IF(HLOOKUP('QC Check'!$K4,'QC Check'!$J4:$O4,1,FALSE)="X","ANODIZE",IF(HLOOKUP('QC Check'!$L4,'QC Check'!$J4:$O4,1,FALSE)="X","HEAT TREAT",IF(HLOOKUP('QC Check'!$M4,'QC Check'!$J4:$O4,1,FALSE)="X","TEFLON",IF(HLOOKUP('QC Check'!$N4,'QC Check'!$J4:$O4,1,FALSE)="X","PLATING",IF(HLOOKUP('QC Check'!$O4,'QC Check'!$J4:$O4,1,FALSE)="X","OTHER", "NOTREAT"))))))

 

[Dave Patton]

Is the formula working for you?

1. Assuming your headings are in Row 3.
Assuming your F3 etc have consistent wording like Treatment1. The 1 will yield the criteria for the formula with
the formula Right(F3,1). The formula in F4 of

=INDEX('QC Check'!$I$3:$O$3,,SMALL(IF('QC Check'!$I4:$O4="x",{1,2,3,4,5,6,7}),1)) can be changed to

=INDEX('QC Check'!$I$3:$O$3,,SMALL(IF('QC Check'!$I4:$O4="x",{1,2,3,4,5,6,7}),RIGHT(F$3)))

This is an Array Formula;consequently, it must be entered with Ctrl+Shift+Enter (CSE) not just Enter. CSE is also required if you edit the formula.


2. Your sheet where you put the "x", could include a formula like the following for the Treatment "None".
This example is in I8 and it says If there is anything in J8:O8, put "" (blank) in I8, otherwise put "x" i.e. None column shows "x" until something is put in J8:O8.

=IF(COUNTA(J8:O8),"","x")

2.Do you use Excel 2007? Yes
Check help for probably the new IFERROR formula; you can suppress #Num! with such a function.
I don't have the exact syntax (see help); I don't use Excel 2007.

 

[GRB]

Dave,
The last formula you gave me worked, THANK YOU!
Can you please teach me how to suppress #Num message when there is no result?
THANKS AGAIN!

 

[GRB]

Would I add the IFERROR formula to the other formula? Where would it go in the sequence?

 

[GRB]

Dave,
Nevermind on the IFERROR - I figured it out. Thanks for your help.

 

[GRB]

Dave,
That worked great! Thanks!