Vlookup & ErrorType

[Chuck]

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,"ErrorType=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?

 

[Dave Peterson]

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]

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