Nesting multiple IF functions with a VLOOKUP

[AlanClark]

Greetings all,

Long time troller, first time poster.

I'm building a spreadsheet that tracks total product packaging weight by multiplying packaging weight per unit by the number of units sold. e.g. 10grams of HDPE per unit x 1000 units sold = 10kg of HDPE.

I have a large worksheet with all of the different products with their associated package weights. All of the simple SUM functions are in place, I just need to bring in the sales figures.

Sales numbers are on a separate sheet and I can bring them in using VLOOKUP. My 2 problems are #N/A's and negative numbers. I used an IF function to replace the negative numbers with zeros, but when I try to add a nested IF function to screen out the #N/A values, it messes it up.

Here is the function that I'm using:=IF(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)<0,0, VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE))

How do I add to this function to also change N/A's to zero?

Thanks in advance.
Alan

 

[Jonmo1]

Welcome to the board..

You don't need an IF to replace negative results with 0.
Try the MAX Function

=MAX(0,VLOOKUP(...))

Then use the usual Error Trapping on the VLOOKUP

=MAX(0,IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)))

Or if you have XL2007+ you can use IFERROR

=IFERROR(MAX(0,VLOOKUP(...)),0)

 

[Audioa84]

Like Jonmo1 said, use iferror or isna.
If your using 2003 then something like this would work.
=IF(ISERROR(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)),0,IF(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)<0,0,VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)))

 

[zorg2780]

Try this to get rid of #N/A's

=IFERROR(IF(VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)<0,0, VLOOKUP(A5,'SALES DATA'!$C$3:$R$1746,12,FALSE)),"")

 

[AlanClark]

Works perfectly, thanks very much. MVP indeed.

 

[AlanClark]

Works perfectly. Thanks very much.