Help with this formula
 

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)))

Help with this formula
 

It were the brackets wot dun it

=IF(ISNA(VLOOKUP($B20,Belgium07ComplaintQ4,{4,5,6, 7,8,9,10,11,12,13},FALSE)),"",VLOOKUP($B20,Belgium 07ComplaintQ4,{4,5,6,7,8,9,10,11,12,13},FALSE))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"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)))

Help with this formula
 

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)))

Help with this formula
 

I have no idea what you're trying to accomplish.

They're entering the formula as an array in a block of cells.

Try this:

A1:E1 = 1,2,3,4,5

A10 = 1

Select the range B10:E10

Enter this formula as an array**:

=VLOOKUP(A10,A1:E5,{2,3,4,5},0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
...
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)))

Help with this formula
 

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)))

Help with this formula
 

If you do that, you'll get the same value, from column 4, in D20:M20. Keep
your selection array {4,5,6,7,8,9,10,11,12,13}if you want the 10 values from
columns 4 through 13 of Belgium07ComplaintQ4 which I assume is at least 13
columns wide.

Tyro


"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.

Help with this formula
 

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)))