Remove #N/A Error


K

kmp09

I am creating a spreadsheet where a user can select different categories
which then populate adjacent cells with information from a second hidden
sheet. Right now I am using this formula and want to remove the #N/A error
that appears when no category has been chosen:

=IF(ISBLANK((VLOOKUP(D11,mmtable,3,FALSE))),((VLOOKUP(D11,mmtable,2,FALSE)))*E11,((VLOOKUP(D11,mmtable,3,FALSE))*E11)+(VLOOKUP(D11,mmtable,2,FALSE)))

I have seen solutions for this along the lines of
=IF(ISNA(formula),""(formula)) but I do not think this will work for me since
I need to keep the both the "if true" and "if false" parts of mine. Any help
would be greatly appreciated.
 
Ad

Advertisements

S

Sean Timmons

=IF(ISNA(VLOOKUP(D11,mmtable,3,FALSE)),"",IF(ISBLANK(VLOOKUP(D11,mmtable,3,FALSE)),VLOOKUP(D11,mmtable,2,FALSE)*E11,VLOOKUP(D11,mmtable,3,FALSE)*E11+VLOOKUP(D11,mmtable,2,FALSE)))

will work, since looking up D11 in the mmtable and returning an #N/A will
bomb out any of the following statementsm this will default to ""
immediately. If no N/A, then it goes through the nested IF statements. I also
removed extraneous parentheses.
 
K

kmp09

This works perfectly. Thank you so much!

Sean Timmons said:
=IF(ISNA(VLOOKUP(D11,mmtable,3,FALSE)),"",IF(ISBLANK(VLOOKUP(D11,mmtable,3,FALSE)),VLOOKUP(D11,mmtable,2,FALSE)*E11,VLOOKUP(D11,mmtable,3,FALSE)*E11+VLOOKUP(D11,mmtable,2,FALSE)))

will work, since looking up D11 in the mmtable and returning an #N/A will
bomb out any of the following statementsm this will default to ""
immediately. If no N/A, then it goes through the nested IF statements. I also
removed extraneous parentheses.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula being changed 4
Splitting Equals 2
suming multiple rates 7
replace N/A with blank 2
VLOOKUP returning #N/A result 2
#N/A error with VLOOKUP function 3
Excel Vlookup Help 0
VLOOKUP 5

Top