| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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"}) -- --- |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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"}) -- --- |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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"}) -- --- |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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"}) -- --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| How can I create an array formula for non-sequential cells | Excel Worksheet Functions | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Linear Mode
