Complicated IF Statement returning #VALUE!

jjacks60

New Member
Joined
Jul 28, 2014
Messages
40
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Lee.

Board Regular
Joined
Dec 15, 2012
Messages
170
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

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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

New Member
Joined
Jul 28, 2014
Messages
40
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

New Member
Joined
Jul 28, 2014
Messages
40

ADVERTISEMENT

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.
 

jjacks60

New Member
Joined
Jul 28, 2014
Messages
40

ADVERTISEMENT

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

jjacks60

New Member
Joined
Jul 28, 2014
Messages
40
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

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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")
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top