Adding another IF function to existing formula

[Nanaia]

I have a criteria I need to add to both true and false sides of an existing IF formula. I want to add

IF(N1="ZINC",REF!A6&REF!A4)

When I try, it's unhappy with how I do it so evidently I haven't learned how to do it right yet. Basically, if N1 has Zinc appear in it, _Z needs to be added regardless of everything else going on.

This is the existing formula:

=IFERROR(IF(CSV!R3<12,(REF!$A$2&REF!B2&REF!$A$3&REF!D2&REF!$A$5&REF!$A$4&REF!$A$7&REF!$A$8&TEXT(REF!W2,"0.000")&REF!$A$9&TEXT(REF!X2,"0.000")&REF!$A$10),(REF!$A$2&REF!B2&REF!$A$3&REF!D2&REF!$A$4&REF!$A$7&REF!$A$8&TEXT(REF!W2,"0.000")&REF!$A$9&TEXT(REF!X2,"0.000")&REF!$A$10)),"")

 

[thisoldman]

I believe there's repetition in your formula that can be removed.

You want to print a concatenated string of cell values.
In that string, if CSV!R3 < 12, insert REF!$A$5 between REF!D2 and REF!$A$4.
In that string, if N1="ZINC", insert "_Z", that is, REF!A6, before REF!$A$4.

Try this:

=IFERROR(REF!$A$2 & REF!B2 & REF!$A$3 & REF!D2
 & IF(CSV!R3<12, REF!$A$5, "")
 & IF(N1="ZINC", REF!A6, "")
 & REF!$A$4 & REF!$A$7 & REF!$A$8 & TEXT(REF!W2,"0.000") & REF!$A$9 & TEXT(REF!X2,"0.000") & REF!$A$10, "")

Without a data sample, I can't tell if the relative and absolute cell references are correct.

If N1 could contain "ZINC", "Zinc", or "zinc", then change the formula to this:

=IFERROR(REF!$A$2 & REF!B2 & REF!$A$3 & REF!D2
 & IF(CSV!R3<12, REF!$A$5, "")
 & IF(UPPER(N1)="ZINC", REF!A6, "")
 & REF!$A$4 & REF!$A$7 & REF!$A$8 & TEXT(REF!W2,"0.000")  & REF!$A$9 & TEXT(REF!X2,"0.000") & REF!$A$10, "")

 

[thisoldman]

Here's a slightly more robust formula. It takes into account that "ZINC", with variations in capitalization, could appear within another string.

=IFERROR(REF!$A$2 & REF!B2 & REF!$A$3 & REF!D2
 & IF(CSV!R3<12, REF!$A$5, "")
 & IF(ISNUMBER(SEARCH("zinc", N1)), REF!A6, "")
 & REF!$A$4 & REF!$A$7 & REF!$A$8 & TEXT(REF!W2,"0.000")  & REF!$A$9 & TEXT(REF!X2,"0.000") & REF!$A$10, "")

 

[Nanaia]

I tried both suggested formulas and neither added "_Z". Here are the data contained in the cells in the formula with the cell location and the information in the cell separated by a colon. The end result should be "INT_H_Z\1323.XLSX//FaceX 53.000 FaceZ 23.250"

CSV!R3: 23.25
N1: ZINC
REFA!10: "
REFA!2: "INT_
REFA!3: \
REFA!4: _N
REFA!5: _Z
REFA!6: .XLSX
REFA!7: //
REFA!8: FaceX
REFA!9: FaceZ
REF!B2: H
REF!D2: 1323

 

[Nanaia]

I just caught that I missed some data, and put the exclamation marks in the wrong spot. My apologies.

REF!W2: 37.000
REF!X2: 11.250

 

[Nanaia]

I got it! I'd never used TEXTJOIN before. It's much cleaner!

=TEXTJOIN(,,REF!$A$2,REF!B2,IF($N$1="ZINC",REF!$A$5,""),REF!$A$3,REF!E2,IF(CSV!R3<12,REF!$A$4,""),REF!$A$6,REF!$A$7,REF!$A$8,TEXT(REF!X2,"0.000"),REF!$A$9,TEXT(REF!Y2,"0.000"),REF!$A$10)

 

[thisoldman]

I'm glad!

I have a standalone version of Excel 2016—TEXTJOIN isn't an option for me. I'm a curmudgeon, I would have stuck with the concatenate operator until I got it right. And I never use the CONCATENATE function.