-------- Original Message -------- From: Johnny Rosenberg <[EMAIL PROTECTED]> Date: Mon 20 Aug 2007 03:19:33 EST
> Or maybe you (Steve) mean that you don't want the #N/A to show up if > the search string is not found in an unsorted range? In that case: > > =IF(A2="";"";IF(ISNA(VLOOKUPA2;$B$1:$C$10;2;0));"";VLOOKUP(A2;$B$1:$C$10;2;0))) > > > This will probably be a bit slow, however, since in some cases the > same VLOOKUP will run twice, so if you have thousands of rows to > search you might notice a difference in speed. Besides, it almost > always take longer to search an unsorted list, since the whole list > has to be searched until the string is found. If the list is sorted, > the search can be stopped whether the string was found or not before > the whole list is searched. > > The line above outputs an empty string either if the search string is > blank or if it doesn't find the search string in the target unsorted > range. If you search sorted ranges, #N/A will never show up if you > tell VLOOKUP that the range is sorted. If it doesn't find the search > string, it just shows the first string that would have been right > after the search string. Your formula could look like this: > > =IF(A2="";"";VLOOKUP(A2;$B$1:$C$10;2;1)) > > or just > > =IF(A2="";"";VLOOKUP(A2;$B$1:$C$10;2)) > > just like John said. > > > J.R. > > > 2007/8/19, John R. Sowden <[EMAIL PROTECTED]>: >> On Sat August 18 2007 13:33, steve wrote: >>> Hi, I use Ver2.2, is there a way to suppress the #N/A when the >>> Vlookup is blank? Many thanks. >> I test to see if the data input cell to test from is empty, as in >> >> =IF(M252="";"";VLOOKUP(M252;$Chart.$A$9:$B$97;2)) >> >> (In this case, I enter a GL account number and pull the text from >> another page in the spreadsheet for the chart of Accounts.) >> >> Hope this helps, >> >> -- John R. Sowden Copy to Steve -- This is a mailing list which exists to support users of OpenOffice.org All answers are given by other users who volunteer their time. If you are not subscribed to this mailing list you may not see all the replies to your query. Please reply to [email protected] only. You should also be aware that all details of messages posted to this list are publicly available to anyone with an Internet connection For mailing list info see: http://www.openoffice.org/mail_list.html For on-line support see: http://support.openoffice.org/index.html For a knowledge base see: http://mindmeld.cybersite.com.au/
signature.asc
Description: OpenPGP digital signature
