Complicated IF Statement returning #VALUE!

[jjacks60]

Hi all.

So I'm having to convert production volume numbers into their case numbers, given a SKU# and it's reference for how many SKU's are in one case.

The problem is there are data mismatches I can do nothing about I.E. on one sheet it reads 11429FZ or maybe 11428PAK1 and on the reference sheet it only reads 11429 or 11428.

I've created the following nested IF function
=IF(FIND("FZ",A1),(LEFT(A1,FIND("FZ",A1)-1)),IF(FIND("PAK1",A1),LEFT(A1,FIND("FZ",A1)-1),"N/A"))
Basically the logic is, If you find FZ in this cell, then look left 1 space back from where FZ is to return the correct value of 11429.
It works fine if the SKU has an FZ, it does not work if it has PAK1.

After evaluating the formula it apparently turns FZ into #VALUE! if it can't find that and ignores the rest of the if.
Is there a way to use an IFERROR maybe or something to have it proceed to looking for PAK1 if it can't find FZ?

After I fix that portion of the formula I'll be using this to divide the original number by the case number
"VLOOKUP(A4,'Case #''s'!A:C,3,0))" A:C is where the case numbers are housed, it's referring A4 for the SKU number to match up with the SKU number on the second list.

I need to remove the FZ or PAK1, so that I can reference the SKU's and divide by the case number in the vlookup.

If I'm over-complicating this, or it could possibly be solved with a macro please say so because I almost would rather use VBA at this point.
Any and all help is appreciated.

 

[Lee.]

Should this not be "PAK1"?

=IF(FIND("FZ",A1),(LEFT(A1,FIND("FZ",A1)-1)),IF(FIND("PAK1",A1),LEFT(A1,FIND("FZ",A1)-1),"N/A"))

 

[bbott]

Should this not be "PAK1"?

=IF(FIND("FZ",A1),(LEFT(A1,FIND("FZ",A1)-1)),IF(FIND("PAK1",A1),LEFT(A1,FIND("FZ",A1)-1),"N/A"))

The first argument of the IF statement will still cause an error. Maybe try:

=IFERROR(IFERROR(LEFT(A1,FIND("FZ",A1)-1),(LEFT(A1,FIND("PAK1",A1)-1))),"N/A")

 

[jjacks60]

Should this not be "PAK1"?

=IF(FIND("FZ",A1),(LEFT(A1,FIND("FZ",A1)-1)),IF(FIND("PAK1",A1),LEFT(A1,FIND("FZ",A1)-1),"N/A"))

Sorry I've been changing it a bunch, but it is listed as "PAK1" on my sheet in the second IF statement.
Like I said before the formula always halts when it can't find the FZ value in A1 and won't proceed to the next IF.

Please advise if you have any input

 

[jjacks60]

The first argument of the IF statement will still cause an error. Maybe try:

=IFERROR(IFERROR(LEFT(A1,FIND("FZ",A1)-1),(LEFT(A1,FIND("PAK1",A1)-1))),"N/A")

PERFECT THANK YOU. I didn't realize you needed two IFERRORs.

 

[bbott]

PERFECT THANK YOU. I didn't realize you needed two IFERRORs.

You're welcome.

 

[jjacks60]

Now for the second portion of the question.
I must divide an index(match returned value by a vlookup for a case reference

Again if you think I'm overcomplicating things I'll try to rethink the approach.

Currently the set up looks like this

30161FZ	Volume returned from Index Match I.E. 432
80014	"
80042PAK1 <tbody> </tbody>	"

<tbody>
</tbody>

I have no trouble returning a value from my index match
=OFFSET(INDEX('Production Data'!M:M,MATCH('Raw Data'!$A4,'Production Data'!$A:$A,0),MATCH($B$1,'Production Data'!$M$1,0)),2,0)

Now I'm looking to divide the number returned from that with a vlookup
=OFFSET(INDEX('Production Data'!M:M,MATCH('Raw Data'!$A4,'Production Data'!$A:$A,0),MATCH($B$1,'Production Data'!$M$1,0)),2,0)/(VLOOKUP(IFERROR(IFERROR(LEFT(A1,FIND("FZ",A1)-1),(LEFT(A1,FIND("PAK1",A1)-1))),A1),'Case #''s'!A:C,3,0))


The Vlookup by itself works fine =VLOOKUP(A2,'Case #''s'!A:C,3,0)
Obviously here I want to replace A2 with the ifferror formula that returns a modified value of A2 IF it contains FZ or PAK1, but just the number is it has no letters.

So I did so
=VLOOKUP(IFERROR(IFERROR(LEFT(A2,FIND("FZ",A2)-1),(LEFT(A2,FIND("PAK1",A2)-1))),A2),'Case #''s'!A:C,3,0)

and IT WORKS for numbers without letters. However if I had a FZ in there I get #N/A returned. The only thing I noticed when evaluating the formula was that 10280FZ becomes "10280" in quotes. Would this change my code and cause it to return N/A#?

 

[jjacks60]

An alternative to this complicated functions is adding to my VBA code.
I grab the SKU numbers from a new raw data set every week, and they vary in which ones have letters on the end.

I assume its possible to create a macro to say okay row end as x, then loop it ("A3" & "A" & X)
I'm just not sure how to use IF Find in vba to remove the text at the end of numbers.

If this is a better idea then I will gladly give it a shot.

 

[bbott]

Now for the second portion of the question.
I must divide an index(match returned value by a vlookup for a case reference

Again if you think I'm overcomplicating things I'll try to rethink the approach.

Currently the set up looks like this

30161FZ	Volume returned from Index Match I.E. 432
80014	"
80042PAK1 <tbody> </tbody>	"

<tbody>
</tbody>

I have no trouble returning a value from my index match
=OFFSET(INDEX('Production Data'!M:M,MATCH('Raw Data'!$A4,'Production Data'!$A:$A,0),MATCH($B$1,'Production Data'!$M$1,0)),2,0)

Now I'm looking to divide the number returned from that with a vlookup
=OFFSET(INDEX('Production Data'!M:M,MATCH('Raw Data'!$A4,'Production Data'!$A:$A,0),MATCH($B$1,'Production Data'!$M$1,0)),2,0)/(VLOOKUP(IFERROR(IFERROR(LEFT(A1,FIND("FZ",A1)-1),(LEFT(A1,FIND("PAK1",A1)-1))),A1),'Case #''s'!A:C,3,0))


The Vlookup by itself works fine =VLOOKUP(A2,'Case #''s'!A:C,3,0)
Obviously here I want to replace A2 with the ifferror formula that returns a modified value of A2 IF it contains FZ or PAK1, but just the number is it has no letters.

So I did so
=VLOOKUP(IFERROR(IFERROR(LEFT(A2,FIND("FZ",A2)-1),(LEFT(A2,FIND("PAK1",A2)-1))),A2),'Case #''s'!A:C,3,0)

and IT WORKS for numbers without letters. However if I had a FZ in there I get #N/A returned. The only thing I noticed when evaluating the formula was that 10280FZ becomes "10280" in quotes. Would this change my code and cause it to return N/A#?

In short, yes. The FIND function is returning characters as text, not numbers. Try changing it to:

=IFERROR(IFERROR(INT(LEFT(A1,FIND("FZ",A1)-1)),INT((LEFT(A1,FIND("PAK1",A1)-1)))),"N/A")

 

[Scott Huish]

An alternative to that first formula.

=IF(ISNUMBER(A1),A1,LEFT(A1,LOOKUP(32768,(FIND({0,1,2,3,4,5,6,7,8,9},A1))))+0)

However, if you are using VBA, you can just use Val to get the numeric portion.

Function GetCode(r)
GetCode = Val(r)
If GetCode = 0 Then GetCode = "N/A"
End Function