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
|