vlookup - #N/A

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: chappy88 (chappy88_at_discussions.microsoft.com)
Date: 07/28/04 

Date: Wed, 28 Jul 2004 05:54:03 -0700

I have a worksheet that I am building for home budgeting. I am using the vlookup function to find a certain category in a 2-column array (unsorted) and return the value in the 2nd column. Everything works fine, I have a fair amount of experience with this, however I want to get rid of the #N/A return when there is no match found.

The reason is: if you have several of these functions in a coulmn returning various data and say 2 of them are returning #N/A, then any simple function referencing the column such as sum will return #N/A also.

Here is my function: =VLOOKUP($B14,$BO$10:$BP$201,2,FALSE)

I use the false in Range_Lookup so I do not have to sort the array each time.

Any ideas on how I can get the function to return a zero instead of the #N/A when there is no match found?? Is there some other function I can nest such as IF??

I greatly appreciate any suggestions.

Thanks, 

-- 
Jeff

Relevant Pages

  • Re: On the complexity of determining whether n numbers are distinct
    ... the minimum number of comparisons for a sort also breaks ... down if you can use the elements to index an array. ... You've just replaced pairwise comparisons with increments, ... comparisons to zero, and decrements, which all still need ...
    (comp.theory)
  • Re: array sorting question
    ... length including zero. ... I want to sort the array of arrays in such a ... way that the interior arrays with the most elements are closest to the ... middle of the main array. ...
    (comp.lang.ruby)
  • Re: "Sorting" assignment
    ... And many others prefer to call partition exchange because "quicksort" ... bin B depending on whether it is greater than, ... If the array is already sorted, this means that you end up ... attempt to sort them. ...
    (comp.programming)
  • Re: A Fast sorting algorithm for almost sorted data
    ... far my compressor has potential but is nowhere near ready. ... It does however make heavy use of sorting. ... which I am currently calling Run sort. ... entire selected run can be added to the sorted output array. ...
    (comp.compression)
  • Re: Arrays of zero length
    ... The minor strangeness is that when you allocate an array to zero size, ... saying something like that allocating an array makes it undefined ... In f77, zero-size was disallowed, ...
    (comp.lang.fortran)