ISNA issue...

**MartinBP** · 08-10-2009, 06:09 AM

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

**Cheeky Charlie** · 08-10-2009, 06:36 AM

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

**DonkeyOte** · 08-10-2009, 06:53 AM

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.

**MartinBP** · 08-10-2009, 07:34 AM

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!

**DonkeyOte** · 08-10-2009, 12:31 PM

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.

**MartinBP** · 08-11-2009, 06:30 AM

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.