#N/A error in VLOOKUP formulas

[dekema]

Hi All


I am new to this blog site and hope you can help with solving an issue with a VLOOKUP formula.


I have been trying to sort this formula to show a total when one of the data entries is not present.

I have googled and searched multiple places but can not resolve it.


this is the formula i have in place:


=SUM(VLOOKUP(100,'Wk Till Data'!$A:$F,5,FALSE)+VLOOKUP(103,'Wk Till Data'!$A:$F,5,FALSE)+VLOOKUP(120,'Wk Till Data'!$A:$F,5,FALSE))


The issue i am finding is that where one of the data sources is not available for the VLOOKUP formula to find (e.g. code "103") an #N/A error code is shown. i need it to work so that where this occurs the missing data is counted as zero or not counted at all so that the rest of the formula can be completed wihout having to adjust the formula manually each time.


I have saved a template of the formula in action at this link address on google docs to be downloaded:

https://docs.google.com/open?id=0Bz48UPiDlVexTUYxQVlFQXhSWEt5dTYxaWlDS1dnUQ


Any help with this will be greatly appreciated


Dan

 

[SirJB7]

Hi, dekema!


Try adjusting your formula as:

=SUM(

IF(ISERROR(VLOOKUP(100,'Wk Till Data'!$A:$F,5,FALSE)),0,VLOOKUP(100,'Wk Till Data'!$A:$F,5,FALSE))+

IF(ISERROR(VLOOKUP(103,'Wk Till Data'!$A:$F,5,FALSE)),0,VLOOKUP(103,'Wk Till Data'!$A:$F,5,FALSE))+

IF(ISERROR(VLOOKUP(120,'Wk Till Data'!$A:$F,5,FALSE)),0,VLOOKUP(120,'Wk Till Data'!$A:$F,5,FALSE))

)


I splitted the formula in three parts for clarifying purposes. Just join the chunks.


Regards!

 

[SirJB7]

Hi, dekema!


I didn't realize until now: if you use a plus sign for normal mathematical addition you don't need to use Excel SUM function, just leave the IFs addition. Or you may replace the plus signs for commas to make SUM be necessary.


Regards!

 

[dekema]

Thank you

Thank you

Thank you


Something so simple can make such a world of difference.


Really do appreciate your help, had been struggling with this one for a while.


Thanks Again

 

[SirJB7]

Hi, dekema!

Glad it helped you. Welcome back whenever wanted or needed.

Regards!