# Nesting multiple IF functions with a VLOOKUP

#### AlanClark

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

Alan

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Jonmo1

##### MrExcel MVP
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

##### Board Regular
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)))

Last edited:

#### zorg2780

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

##### New Member
Works perfectly, thanks very much. MVP indeed.

#### AlanClark

##### New Member
Works perfectly. Thanks very much.

Replies
1
Views
173
Replies
1
Views
299
Replies
5
Views
188
Replies
2
Views
182
Replies
3
Views
370

1,195,992
Messages
6,012,741
Members
441,724
Latest member
Aalbid

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

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