+ Reply to Thread
Results 1 to 7 of 7

Adding ISNA to long nested IF statements with vlookups

  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    MD
    MS-Off Ver
    Excel 2010
    Posts
    5

    Adding ISNA to long nested IF statements with vlookups

    Hi, I'm trying to add the ISNA formula to a long formula with 12 IF statements in it. I have a spreadsheet that needs to look into different places based on the account name listed. It works fine, but I need to get rid of the #N/A in order for my pivot table to work. When I've tried doing it myself, I either get a message saying I'm missing a parenthasis or that it's invalid. Here is what the formula looks like now.

    =IF($C2="TOTAL SALES AND REVENUES",(VLOOKUP($A2,Forecast!$1:$15000,105,FALSE)),IF($C2="TOTAL FIELD CON",(VLOOKUP($A2,Forecast!$1:$15000,117,FALSE)),IF($C2="TOTAL DIVISION OVERHEAD",(VLOOKUP($A2,Query2!$1:$1048576,75,FALSE)),IF($C2="FRINGE / INSURANCE INCOME",(VLOOKUP($A2,Query2!$1:$1048576,51,FALSE)),IF($C2="TOTAL OTHER INCOME",(VLOOKUP($A2,Query2!$1:$1048576,63,FALSE)),IF($C2="TOTAL GROSS PROFIT",(VLOOKUP($A2,Query2!$1:$1048576,69,FALSE)),IF($C2="TOTAL PRODUCT COST",(VLOOKUP($A2,Query2!$1:$1048576,9,FALSE)),IF($C2="TOTAL CONTROLLABLE COST",(VLOOKUP($A2,Query2!$1:$1048576,27,FALSE)),IF($C2="TOTAL NON-CONTROLLABLE COST",(VLOOKUP($A2,Query2!$1:$1048576,33,FALSE)),IF($C2="PURCHASING INCOME",(VLOOKUP($A2,Query2!$1:$1048576,45,FALSE)),IF($C2="TOTAL ADMIN. LABOR",(VLOOKUP($A2,Query2!$1:$1048576,15,FALSE)),IF($C2="TOTAL REGULAR / HOURLY LABOR",(VLOOKUP($A2,Query2!$1:$1048576,21,FALSE))))))))))))))

    Any ideas? Thanks!!

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Adding ISNA to long nested IF statements with vlookups

    You could wrap an IFERROR around the whole thing:

    =IFERROR(yourformula,"")

    "" returns blank
    "text" returns text
    1 returns 1
    or you can use another fomula.
    Say thanks, click *

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding ISNA to long nested IF statements with vlookups

    That's a real beauty!

    Just put that whole thing inside the IFERROR function:

    =IFERROR(IF($C2="TOTAL.....),"")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-06-2013
    Location
    MD
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Adding ISNA to long nested IF statements with vlookups

    Wow, that was so much easier than I was trying to do. Thanks!!!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Adding ISNA to long nested IF statements with vlookups

    You're welcome. Thanks for the feedback!

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Adding ISNA to long nested IF statements with vlookups

    Just a shot in the dark, see if this works:

    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Adding ISNA to long nested IF statements with vlookups


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1