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

[tskogstrom]

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";"Industry";"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";"Industry";"B&I";"R&L";"Others";"Others"})


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

Happy to all suggestions,
Tskogstrom

 

[RichardSchollar]

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","Others"}

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

 

[RichardSchollar]

Or possibly this:

={"Telecom";"Public & Healthcare";"Industrial & Utilities";"Banking &
Insurance";"Retail &
Logistics";"Other";"Company","Telecom";"P&H";"Industry";"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

 

[tskogstrom]

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","Others"}

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","Others"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

 

[RichardSchollar]

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","Others"},2,0)

Richard

 

[tskogstrom]

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:

 

[Bob Phillips]

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)


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","Others"}

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","Others"})

Why "Others2" ?What have I missed??

/regards
tskogstrom




RichardSchollar skrev:

 

[RichardSchollar]

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

 

[Bob Phillips]

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

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

 

[tskogstrom]

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:

 

[tskogstrom]

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"\"Others"}

/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

 

[Bob Phillips]

Wow! Never came across that before.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)