Vlookup & ErrorType
 

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

Vlookup & ErrorType
 

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

--

Dave Peterson

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