I want to nest the following statements: =IF(ISERROR(FIND(\"-\",A2,FIND(\"-\",A2

Question

I want to nest the following statements:

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XX"

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XXX"

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XXXX"

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XXXXX"

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XXXXXX"

=IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"XXXXXX"

At the moment I have this running over 6 columns with a 7th column containing:

=IFERROR(VLOOKUP(A2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(F2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(G2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(H2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(I2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(J2,MPL!$A:$V,1,FALSE),IFERROR(VLOOKUP(K2,MPL!$A:$V,1,FALSE),"")))))))

The logic behind this is in Cell A2, there is a part number which will have a combination of letters/numbers at the end e.g. AA-BC1234-234DE, for the look up to work, it needs to end in -XX or with multiple X's e.g. AA-BC1234-XX. There is no way to determin whether it is XX or XXXXXX.

Is there a way to nest the first series of statements, to make the last statement simpler and not use as many columns in the spreadsheet?

Thanks in advance!.! :_)

Explanation / Answer

I feel you may use this compact formula instead of your above formula.

I have just used Star "*" instead of "X"/"Xs" shown in bold, it should take care your requirement and will not need any additional 6 columns.

=IFERROR(VLOOKUP(IF(ISERROR(FIND("-",A2,FIND("-",A2,1)+1)),A2,LEFT(A2,FIND("-",A2,FIND("-",A2,1)+1)))&"*",MPL!$A:$V,1,FALSE),"")!!

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.