Help with this formula

[LiveUser]

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,Belgium07ComplaintQ4,{4,5,6,7,8,9,10,11,12,13},0)))

 

[Bob Phillips]

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,Belgium07ComplaintQ4,{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)

 

[Tyro]

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

 

[T. Valko]

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)

 

[LiveUser]

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]

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

 

[T. Valko]

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($A2020),0)),"",VLOOKUP($B20,Belgium07ComplaintQ4,COLUMNS($A2020),0))

Copy across as needed.