Vlookup & ErrorType
On Mon, 30 Mar 2009 08:47:56 -0500, Dave Peterson
wrote:
Dave,
The if(iserror ... technique gives the desired results. Many thanks for the
suggestion.
I'm trying to fill a calendar with data from an array. The first column in the
array contains dates, formatted 3/4/97, not contiguous. The second column
contains text statements. I want to see blank spaces when there is no date in
the array corresponding to the date of a specific cell in the calendar, not a
lot of "#N/A".
The program has two arrays, the other array has a row of days (1-31) and
columns of months (1-12). The cells in that array can simply be left blank if
need be, so I don't have to check for errors.
The cells in the calendar have two Vlookups: =Vlookup{array1} &
Vlookup{array2}. To have array2 'work' like array 1, it would have to ba a
three dimensional array, days, months, years. I don't know if Excel can handle
three dimensional arrays or not, but it doesn't make any difference because
populating such an array would be a Herculean task and not really be worth the
effort.
Chuck
Check to see if it's an error first:
=if(iserror(vlookup(...)),if(error.type(...)... ), "not an error")
I'm not sure what you're doing, but is there a reason for not using:
=if(isna(vlookup(....)),"no match",vlookup(...)))
Chuck wrote:
Excel 97
The following equation is in cell B11. The array named arrayA is in the same
sheet as the equation.
IF(ERROR.TYPE(VLOOKUP(A11,arrayA,2,FALSE))=7,"Erro rType=7","ErrorType<7")
If A11 does not have a corresponding value in the first column of arrayA, the
equation correctly returns, ErrorType=7.
If A11 does have a corresponding value in the first column of arrayA, the
equation incorrectly returns #N/A which is Error.Type 7
Why doesn't it return, ErrorType<7?
--
Chuck
|