+ Reply to Thread
Results 1 to 6 of 6

ISNA issue...

  1. #1
    Registered User
    Join Date
    08-10-2009
    Location
    sussex
    MS-Off Ver
    Excel 2002
    Posts
    3

    ISNA issue...

    Hi all, new to the site but it seems to be the place to get the right help with a troublesome excel formula!

    Here's the formula:
    =IF(AND(VLOOKUP(C634,'[CustomerBactosolInvoicing.xls]Customer Bactosol Invoicing'!$A$2:$J$2000,9,FALSE),N634>""),"",VLOOKUP(C634,'[CustomerBactosolInvoicing.xls]Customer Bactosol Invoicing'!$A$2:$J$2000,9,FALSE))

    My problem is that I've had to enter the AND function to get the results to perform correctly (as there can be multiple duplication of the lookup values)
    However, I can no longer use the ISERROR or ISNA functions as putting them in gives me an error telling me there are too many arguments, or the formula is incorrect, which means I end up with a column mainly made up of #N/A when I want a nice empty cell if there's nothing to fill in.

    Is there a shorter way to do this formula? Or am I missing a trick? Unfortunately I cannot attach a copy of the workbooks I am working from as they are confidential.

    Thanks for any help!

    Martin
    Last edited by MartinBP; 08-10-2009 at 06:23 AM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: ISNA issue...

    The best general principle with vlookups and iserrors is to use two columns:
    =if(iserror(vlookup()),"",vlookup())
    performs all lookups twice - one to check the error and once to display it if no error
    while:
    =vlookup() | =if(iserror(<-that cell),"",<-that cell)
    is distinctly more efficient
    Note - columns can be hidden/unhidden quickly with Ctrl+0 and Ctrl+Shift+0 (unhide by selecting cells "across" your hidden column)

    I think this would implicitly solve your problem, though if it doesn't perhaps you could try explaining it a different way as I don't think I quite understand where you iserror was going to be anyway...

    HTH

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: ISNA issue...

    There is another 1 cell option which avoids double evaluation but in part it depends upon a consistent output - by which I mean the result of the VLOOKUP etc is always a text string or always a number ... not a mix.

    To illustrate, when returning a text string you can use:

    =LOOKUP(REPT("Z",255),CHOOSE({1,2},"",VLOOKUP(....)))

    (substituting the "" for whatever you want to appear in case of VLOOKUP returning error)

    When returning numbers we can adjust the LOOKUP accordingly to:

    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,VLOOKUP(....)))

    If the 0's are to be hidden we can use a Custom Format to do this.

    There are obvious issues with this approach but both can prove very handy techniques... they work because LOOKUP expects values to be listed in Ascending order, it finds the last value <= criteria thus if you set critieria to be immense then it will always return the last value found in the array of values and will ignore values that are not of the same type as the criteria - this includes errors... so if the VLOOKUP returns an error it returns the default value (Null,0) else it returns the result of the VLOOKUP.

  4. #4
    Registered User
    Join Date
    08-10-2009
    Location
    sussex
    MS-Off Ver
    Excel 2002
    Posts
    3

    Re: ISNA issue...

    thanks for the replies so far, not sure they will apply to my problem, I'll try to explain a little better.

    Basically I have 2 workbooks for direct deliveries to customers. One is a record of orders made from my company to a supplier, this sheet contains our order number (in ascending order), our customer details (unique ref. number and company name) as well as the rest of the order details. We also fill in other cells for each order when we recieve an order confirmation e-mail, despatch notification, and invoice number.

    The second workbook is built to pick up any orders from the first workbook which we have recieved an invoice for, this then shows us which of our customers we need to invoice. Our man in invoicing will then fill in 2 cells with our invoice number and the invoice date.

    Then we go back to the first workbook and my above problematical formula. The formula needs to pick up any of our invoice numbers entered onto the 2nd workbook. However, as we can have multiple orders from one customer, I needed the formula to perform an AND VLOOKUP, to lookup their unique ref. number, and our suppliers invoice number. If I didn't have the AND lookup then the lookup would return the customers first invoice number for each of their orders, which would obviously be incorrect.

    The above formula works very well returning the correct invoice numbers onto the first workbook, but it will enter #N/A into the cell if there is not a supplier invoice number present. I just want to remove the #N/A's however if I enter an ISERROR or ISNA function after the IF I get an error telling me I have too many arguments in the forumla.

    Hope thats not as confusing as it reads!

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: ISNA issue...

    It might be easier if you attach representative samples of both files (removing anything otherwise deemed confidential) - if we can see what you're working with in terms of ranges / data etc things will become a lot clearer.

  6. #6
    Registered User
    Join Date
    08-10-2009
    Location
    sussex
    MS-Off Ver
    Excel 2002
    Posts
    3

    Re: ISNA issue...

    Problem solved now, I've added a unique referance to one of the sheets to use in the vlookup for the second sheet, it seems to work well so far!

    Thanks again for the help, I have no doubt I'll be back with more questions in the very near future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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