Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / February 2008

Tip: Looking for answers? Try searching our database.

Help with this formula

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LiveUser - 01 Feb 2008 18:17 GMT
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 - 01 Feb 2008 18:38 GMT
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))

Signature

---
HTH

Bob

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

> What am I doing wrong?
> I know I have to do control shift enter for an array but I am receiving an
[quoted text clipped - 3 lines]
>
> =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)))
Tyro - 01 Feb 2008 18:57 GMT
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

> What am I doing wrong?
> I know I have to do control shift enter for an array but I am receiving an
[quoted text clipped - 3 lines]
>
> =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)))
T. Valko - 01 Feb 2008 19:33 GMT
>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)

Signature

Biff
Microsoft Excel MVP

> Your vlookup is wrong. The format is:
> vlookup(lookup_value,table_array,col_index_number,range_lookup) For
[quoted text clipped - 28 lines]
>>
>> =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)))
LiveUser - 01 Feb 2008 20:04 GMT
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.

> Your vlookup is wrong. The format is:
> vlookup(lookup_value,table_array,col_index_number,range_lookup) For example
[quoted text clipped - 24 lines]
> >
> > =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)))
Tyro - 01 Feb 2008 20:20 GMT
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

> Would I be better off using this formula?
>
[quoted text clipped - 5 lines]
>
> Thank you.
T. Valko - 01 Feb 2008 21:02 GMT
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,Belgium07ComplaintQ4,COLUMNS($A20:D20),0))

Copy across as needed.

Signature

Biff
Microsoft Excel MVP

> Would I be better off using this formula?
>
[quoted text clipped - 42 lines]
>> >
>> > =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)))
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.