The array formula is more efficient calculation-wise. However, once you
enter as a range array you can't change a single element of that array, you
have to make the change to the entire array. For example, you couldn't
delete one of the columns that's part of the array.

You can get the column_index_number to increment like this (normally
entered, not an array):

=IF(ISNA(VLOOKUP($B20,Belgium07ComplaintQ4,COLUMNS ($A20:D20),0)),"",VLOOKUP($B20,Belgium07ComplaintQ 4,COLUMNS($A20:D20),0))

Copy across as needed.


--
Biff
Microsoft Excel MVP


"LiveUser" wrote in message
...
Would I be better off using this formula?

=IF(ISNA(VLOOKUP($B20,Belgium07ComplaintQ4,4,0))," ",VLOOKUP($B20,Belgium07ComplaintQ4,4,0))

Really all I am trying to do is to get rid of the #N/A's and copy across
from D20 to M20 without having to change the column index number in each
cell. Doesn't happen when I copy down.

Thank you.



"Tyro" wrote:

Your vlookup is wrong. The format is:
vlookup(lookup_value,table_array,col_index_number, range_lookup) For
example
if A1 contains b then
=VLOOKUP(A1,{"a",100;"b",200;"c",300},2,0) returns 200. The lookup_value
is
the value you're trying to find. table_array is the list you're looking
in.
The table_array is specified by the array constant
{"a",100;"b",200;"c",300}where the commas are column separators and the
semicolons are row separators.
In this case there are 3 rows with 2 columns each row. Row 1 column 1 has
"a", row 1 column 2 has 100, row 2 column 1 has "b", row 2 column 2 has
200,
row 3 column 1 has "c" and row 3 column 2 has 300. The
column_index_number
is 2 - i.e return the value in column 2 if the lookup_value is found in
column 1
of the table_array. The range_lookup is 0 (FALSE) meaning there has to be
an
exact match. In this case A1 contains b, so the lookup function finds an
exact match of in row 2 column 1 of the table, the b, and returns
column
2 - i.e. 200 From the way you have constructed your formula, I have no
idea what you're trying to accomplish.

Tyro

"LiveUser" wrote in message
...
What am I doing wrong?
I know I have to do control shift enter for an array but I am receiving
an
error saying my formula is wrong. Can I even so this?


Thank you.

=IF(ISNA(vlookup($B20,Belgium07ComplaintQ4,{4,5,6, 7,8,9,10,11,12,13},0),"",vlookup($B20,Belgium07Com plaintQ4,{4,5,6,7,8,9,10,11,12,13},0)))