Difficulty figuring out were to put IFNA

I have been struggling trying to figure out were to put the function IFNA in a formula that I am working on. The formula is as follows:

=((IF(ISERROR(VLOOKUP($A$211,Dough_amounts,2,FALSE)),(VLOOKUP($A$211,Dough_amounts_2,2,FALSE)),(VLOOKUP($A$211,Dough_amounts,2,FALSE)))*VLOOKUP($A$211,weight_cac_mixin,5,FALSE)))

I am needing to look up a specific product(A211) that might be in two different look up areas (dough_amount, and dough_amounts_2). Then take the amount of the product (column 2) and multiply it by the weight of the product (weight_cac_mixin). The ending result is the total amount of dough needed to produce.
I am running into issues when that product isn't being produced that day and doesn't show up in either of the two above search areas. It gives me a #N/A which causes problems with the formula that comes after that. The production sheet will be used earlier versions of excel (1997 to 2003). Any help that you can give would be great.

Hi BakerBoy,

I think you can just wrap the whole formula in IFNA like this:

=IFNA(((IF(ISERROR(VLOOKUP($A$211,dough_amounts,2,FALSE)),(VLOOKUP($A$211,dough_amounts_2,2,FALSE)),(VLOOKUP($A$211,dough_amounts,2,FALSE)))*VLOOKUP($A$211,weight_cac_mixin,5,FALSE))),0)

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David

This didn't work, it said there was too many arguments for the formula.

I am using 2010 and the IFNA function works seems to work well. The thing is that this workbook goes out to 87 different locations with 2003 upto the latest edition.

IFNA() function
APPLIES TO: Excel 2016, Excel 2013, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones. Seems you've extended version of Ex 2010 or UDF

Sorry BakerBoy,

One tends to forget when some of the functions were introduced! As Sandy pointed out, IFNA() only came into use in 2013. Thus, you have to make the formula quite long for it to be backwards compatible. Thus:

Please Login or Register  to view this content.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

IMO, this formula from #9 is too long (visual)

Formula:

Please Login or Register  to view this content.

so:
in Name Manager define names:
for =VLOOKUP($A$211,dough_amounts,2,0), e.g. VLDA
for =VLOOKUP($A$211,dough_amounts_2,2,0), e.g. VLDA2
for =VLOOKUP($A$211,weight_cac_mixin,5,0), e.g. VLWCM
and in effect you will get:

Formula:

Please Login or Register  to view this content.

I think that it is more clear and shorter