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 >
