Array as a "named range" - formula ok in cells, but error as "named range"
 

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

From Excel help (and I didn't consider this possibility - my apologies)

Important The values in array must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not
give the correct value. Uppercase and lowercase text are equivalent.

Which I presume is why you are getting an incorrect result.

You can correct this by using the VLOOKUP form (at least the following
worked for me):

=VLOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"},2,0)

Richard


tskogstrom wrote:

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

Hi,

I tested with a reference to a two column (Lookup(array)) instead and
it gives the same result. I thought at first it had something to do
with the "&" or blanks, but "Telecom" also give "Others". Also tested
Lookup(vector), definingt the two columns singlehanded but with same
result.

It must have something to do with the usage of Lookup formula, doesn't
it?

/regards
tskogstrom



RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public & Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"tskogstrom" wrote in message
ps.com...
Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't
accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public & Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---

Array as a "named range" - formula ok in cells, but error as "named range"
 

Yeah, but I was concocting mine whilst you replied, so I hadn't seen it at
that point.

I'll take mine without the tonic please.

Bob


"RichardSchollar" wrote in message
ups.com...
That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public &
Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---

Array as a "named range" - formula ok in cells, but error as "named range"
 

Thank you richard,
everything is rock'n roll now. I'll change to my own national setting
now, wish me luck now it will become right syntax by auto ....

/regards
tskogstrom


RichardSchollar skrev:

From Excel help (and I didn't consider this possibility - my apologies)

Important The values in array must be placed in ascending order:
...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not
give the correct value. Uppercase and lowercase text are equivalent.

Which I presume is why you are getting an incorrect result.

You can correct this by using the VLOOKUP form (at least the following
worked for me):

=VLOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"},2,0)

Richard


tskogstrom wrote:

Hi,
thanks for trying. I have now changed national settings to UK and this
is OK:
{"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"}

Still my Lookup formula doesn't work - BUT nor in the worksheet:

In the worksheet cell this formula produce "2" as it should:
=LOOKUP("b",{"a",1;"b",2;"c",3})

Shouldn't this formula in that case produce "P&H"? It doesn't, whatever
I write as "lookup_value", it gives "Others2" as result. The same I
recieve from the named range with same syntax:

=LOOKUP("Public & Healthcare",{"Telecom","Telecom";"Public &
Healthcare","P&H";"Industrial & Utilities","Industry";"Banking &
Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others2";"Company","Othe rs"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Indu stry";"B&I";"R&L";"Othe*rs";"Others"}


I've just taken out the };{ from the middle (this was causing it to
error), and replaced the semi-colon between "Company" and "Telecom"
with a comma (",").

Hope this helps!

Richard


RichardSchollar wrote:

Hi Tskogstrom

If I was doing that in the UK, I would write it as:

={"Telecom","Telecom";"Public & Healthcare","P&H";"Industrial &
Utilities","Industry";"Banking & Insurance","B&I";"Retail &
Logistics","R&L";"Other","Others";"Company","Other s"}

You may have to swap the "," and ";" around. Basically, it forms a
grid where the columns are separated by "," and the rows by ";".

Hope this helps!

Richard


tskogstrom wrote:

I want to have array formula as a named range, but Excel doesn't accept
this formula. Maybe it is the "&" sign, or? How should I write? I have
Swedish signs, therefore use ";" instead of "," etc, I guess.

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"}


THIS worksheet formula is accepted into an ordinary cell.. I recieve
"Others" in the cell:

=LOOKUP("Company";{"Telecom";"Public & Healthcare";"Industrial &
Utilities";"Banking & Insurance";"Retail &
Logistics";"Other";"Company"};{"Telecom";"P&H";"In dustry";"B&I";"R&L";"Others";"Others"})


So - any idea how it should be as a named range formula?

Happy to all suggestions,
Tskogstrom

Array as a "named range" - formula ok in cells, but error as "named range"
 

Bob and Richard,
Following works with Swedish national settings, if you need to know
sometime... "," turns to "\" and , turns to ";". Of course.

={"Telecom"\"Telecom";"Public & Healthcare"\"P&H";"Industrial &
Utilities"\"Industry";"Banking & Insurance"\"B&I";"Retail &
Logistics"\"R&L";"Other"\"Others2";"Company"\"Othe rs"}

/regards
tskogstrom




Bob Phillips skrev:

Yeah, but I was concocting mine whilst you replied, so I hadn't seen it at
that point.

I'll take mine without the tonic please.

Bob


"RichardSchollar" wrote in message
ups.com...
That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public &
Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---

Array as a "named range" - formula ok in cells, but error as "named range"
 

Wow! Never came across that before.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"tskogstrom" wrote in message
ups.com...
Bob and Richard,
Following works with Swedish national settings, if you need to know
sometime... "," turns to "\" and , turns to ";". Of course.

={"Telecom"\"Telecom";"Public & Healthcare"\"P&H";"Industrial &
Utilities"\"Industry";"Banking & Insurance"\"B&I";"Retail &
Logistics"\"R&L";"Other"\"Others2";"Company"\"Othe rs"}

/regards
tskogstrom




Bob Phillips skrev:

Yeah, but I was concocting mine whilst you replied, so I hadn't seen it
at
that point.

I'll take mine without the tonic please.

Bob


"RichardSchollar" wrote in message
ups.com...
That's what I said! In a round about way. Point me to the gin &
tonics...


Bob Phillips wrote:

It's the order

=LOOKUP("Public & Healthcare",{"Banking &
Insurance","B&I";"Company","Others";"Industrial &
Utilities","Industry";"Other","Others2";"Public &
Healthcare","P&H";"Retail
& Logistics","R&L";"Telecom","Telecom"})

--
---