# Complicated IF Statement returning #VALUE!

#### jjacks60

##### New Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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"))

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")

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.

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.

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

You're welcome.

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
30161FZVolume 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#?

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.

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
30161FZVolume 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")

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.

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

Replies
22
Views
399
Replies
3
Views
132
Replies
6
Views
210
Replies
6
Views
136
Replies
10
Views
442

### Forum statistics

1,203,172
Messages
6,053,888
Members
444,692
Latest member
Queendom ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back