Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |