I'm trying to get an Excel workbook (*.xls) to work in Calc, as it appears they don't automatically just work in Calc but require some conversions to be made.
I'm trying to get it to work with a Named Range (eg: Descriptions), which isn't working, so I just tried to use a cell range (eg: A1:A3), which also does not appear to work.
http://wiki.openoffice.org/wiki/Documen ... H_function
doesn't mention if a cell range can be used as the 2nd parameter (well 1st parameter in Excel but 2nd in Calc).
eg: without a cell range as a parameter, SEARCH works fine.
SEARCH ("Coles"; "Kmart Coles Amart")
Eg: with a cells range as a parameter, SEARCH fails.
If A1 contains "Kmart", A2 contains "Coles" & A3 contains "Amart"...
SEARCH ("Coles", A1:A3)
fails as it returns #VALUE!
[Solved] Can SEARCH function use a cell range?
[Solved] Can SEARCH function use a cell range?
Last edited by Hagar Delest on Thu Mar 14, 2013 10:08 pm, edited 3 times in total.
Reason: tagged solved.
Reason: tagged solved.
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
Re: Can SEARCH function use a cell range?
I don't see that the parameter order is different in Excel and Calc. Here is the Excel Help description of SEARCH() and SEARCHB()
And here is the Calc HelpSEARCH(find_text, within_text, [start_num])
SEARCHB(find_text, within_text, [start_num])
The SEARCH and SEARCHB function syntax has the following arguments:
find_text Required. The text that you want to find.
within_text Required. The text in which you want to search for the value of the find_text argument.
start_num Optional. The character number in the within_text argument at which you want to start searching.
I also can't get the Excel SEARCH() function to accept a cell range as an argument unless I enter it as an array function, which also works in Calc and requires as many cells as are in the searched range. How exactly are you using SEARCH() in EXCEL?SEARCH("FindText"; "Text"; Position)
FindText is the text to be searched for.
Text is the text where the search will take place.
Position (optional) is the position in the text where the search is to start.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Can SEARCH function use a cell range?
SEARCH returns the position number where one string can be found in the text of one cell or #VALUE in case of no match. Entered as an array function it returns many position numbers indicating where a string can be found in the respective referenced cell.
This is the same in all Excel versions, in Calc and in Gnumeric.
This is the same in all Excel versions, in Calc and in Gnumeric.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Can SEARCH function use a cell range?
I can't use Office anymore, which is another story (& I've filled in a counterfeit report with microsoft).
My Excel workbook contains these formulas to help summarise transaction descriptions (row B, eg: "COLES SYDNEY AUS 4321") in my bank statements:
=LOOKUP(2^15,SEARCH(Descriptions,$B2),Categories)
=LOOKUP(2^15,SEARCH(Descriptions,$B2),SubCats)
Categories (eg: "Food/Other"), SubCats (eg: "Coles") & Descriptions (eg: "COLES", extracts of the transaction descriptions) are all global named ranges (in another worksheet). I'll try to upload an example.
This works fine in Excel (if a string is not found, it displays "#N/A"), however when I open it in Calc, all cells with 1 of these formulas in it returns #VALUE!.
My Excel workbook contains these formulas to help summarise transaction descriptions (row B, eg: "COLES SYDNEY AUS 4321") in my bank statements:
=LOOKUP(2^15,SEARCH(Descriptions,$B2),Categories)
=LOOKUP(2^15,SEARCH(Descriptions,$B2),SubCats)
Categories (eg: "Food/Other"), SubCats (eg: "Coles") & Descriptions (eg: "COLES", extracts of the transaction descriptions) are all global named ranges (in another worksheet). I'll try to upload an example.
This works fine in Excel (if a string is not found, it displays "#N/A"), however when I open it in Calc, all cells with 1 of these formulas in it returns #VALUE!.
Last edited by shell_l_d on Wed Mar 13, 2013 11:15 am, edited 1 time in total.
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
Re: Can SEARCH function use a cell range?
Googled array function ... http://wiki.openoffice.org/wiki/Documen ... ing_Arrays > see 'Array Formulas' & 'Array Functions'
Tried Ctrl+Shift+Enter instead of pressing Enter to make it an array formula (or by ticking the 'Array' checkbox if using the formula wizard.
Not a solution.
Tried Ctrl+Shift+Enter instead of pressing Enter to make it an array formula (or by ticking the 'Array' checkbox if using the formula wizard.
Not a solution.
Last edited by shell_l_d on Wed Mar 13, 2013 11:16 am, edited 1 time in total.
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
Re: Can SEARCH function use a cell range?
Ok, hope these 2 files will help to explain the Excel workbook & how I can get it to work in Calc. 
- Attachments
-
- StatementFormulas.xls
- Sample attempt at providing a similar workbook & trying to get the formulas to work.
- (9.5 KiB) Downloaded 823 times
-
- Extracts from the Excel Workbook
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
-
ken johnson
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: [Unsolved/too hard] Can SEARCH function use a cell range
Try...
array entered in F3 then Ctrl+Filled down to F7, and...
array entered in G3 then Ctrl+Filled down to G7.
Ken Johnson
Code: Select all
INDEX(Categories;SUM(IF(ISNUMBER(SEARCH(Descriptions;A3));ROW(Descriptions)-MIN(ROW(Descriptions))+1;"")))Code: Select all
INDEX(SubCats;SUM(IF(ISNUMBER(SEARCH(Descriptions;A3));ROW(Descriptions)-MIN(ROW(Descriptions))+1;"")))AOO 4.1.3 on Ms Windows 10
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
Re: [Unsolved/too hard] Can SEARCH function use a cell range
The subject is : Can SEARCH function use a cell range?
Yes.
The problem is with the LOOKUP function :
This function is intended to work with a search_array sorted is ascending order.
1. When the search array is not in ascending order, LOOKUP returns random result (or #N/A) in both Excel and Calc.
2. It appears that when the search_criterion is bigger than all the search_array values, LOOKUP returns the last value in both Excel and Calc.
But : As Villeroy says, it is not documented. (works by accident as LOOKUP is not intended to work this way).
The only difference between Excel and Calc is that LOOKUP ignored error result given by SEARCH, Calc not.
That's why this empirical formula don't works in Calc.
About Ken formula above,
Works if A3 contains only one of the terms in Descriptions' range.
Replacing SUM with MAX will returns the last term found if several.
Yes.
The problem is with the LOOKUP function :
This function is intended to work with a search_array sorted is ascending order.
1. When the search array is not in ascending order, LOOKUP returns random result (or #N/A) in both Excel and Calc.
2. It appears that when the search_criterion is bigger than all the search_array values, LOOKUP returns the last value in both Excel and Calc.
But : As Villeroy says, it is not documented. (works by accident as LOOKUP is not intended to work this way).
The only difference between Excel and Calc is that LOOKUP ignored error result given by SEARCH, Calc not.
That's why this empirical formula don't works in Calc.
About Ken formula above,
Works if A3 contains only one of the terms in Descriptions' range.
Replacing SUM with MAX will returns the last term found if several.
LibreOffice 6.4.5 on Windows 10
Re: [Solved] Can SEARCH function use a cell range?
Thanks so much for your responses gerard24 & ken johnson.
gerard24, in Excel that LOOKUP formula works perfectly fine, it only returns #N/A if there is no match found at all.
In my personal spreadsheet (which I didn't attach as it has personal data in it) , the Categories are in sorted order followed by Descriptions (which are Unique), to make it easier to manage & update when I need to add new Categories, SubCats & Descriptions.
Yes the 2^15 (2 to the power 15) forces the LOOKUP to use the last integer/match found & if no integer/match exists, then it returns #N/A.
To my understanding (which may be wrong as I am no expert) ...
=LOOKUP(2^15,SEARCH(Descriptions,$A3),Categories) which returns "Food/Other"
=LOOKUP(2^15,SEARCH(Descriptions,$A3),SubCats) which returns "Coles"
the 1st param in LOOKUP is processed: 2^15 (2 to the power 15, ^ meaning power, just like in MikTek/LaTeX) = 32768
the 2nd param in LOOKUP is processed: the SEARCH formula, which returns an array (based on the 'Descriptions' named range) of values which are #N/A (where no match found) & integer positions where matches are found.
the 3rd param in LOOKUP is processed: an array, it returns the array of 'Categories' (or 'SubCats').
the partially processed formula looks like this: LOOKUP( 32768, {"#N/A", "#N/A", 1, "#N/A", "#N/A"}, {"Fuel/Other", "Fuel/Other", "Food/Other", "Food/Other", "Food/Other"} )
The LOOKUP then finds the last integer found in the first array (1, the 3rd element, which represents "COLES" from 'Descriptions') & returns the string from the relevant position (3rd element) from the 2nd array ("Food/Other" for 'Categories' or "Coles" for 'SubCats'). All thanks to Barry Houdini.
None of the arrays in the excel LOOKUP & SEARCH formulas given use array formula's, just arrays.
However ... the only way I was able to get SEARCH to work in Calc with an array was by using an array formula (which I didn't find documented), which would not work for the formula (again, I could be wrong).
Hence why I posted the question here in the first place, to ask if SEARCH could take a cell range, also known as an 'array' (not an array formula).
gerard24, in Excel that LOOKUP formula works perfectly fine, it only returns #N/A if there is no match found at all.
In my personal spreadsheet (which I didn't attach as it has personal data in it) , the Categories are in sorted order followed by Descriptions (which are Unique), to make it easier to manage & update when I need to add new Categories, SubCats & Descriptions.
Yes the 2^15 (2 to the power 15) forces the LOOKUP to use the last integer/match found & if no integer/match exists, then it returns #N/A.
To my understanding (which may be wrong as I am no expert) ...
=LOOKUP(2^15,SEARCH(Descriptions,$A3),Categories) which returns "Food/Other"
=LOOKUP(2^15,SEARCH(Descriptions,$A3),SubCats) which returns "Coles"
the 1st param in LOOKUP is processed: 2^15 (2 to the power 15, ^ meaning power, just like in MikTek/LaTeX) = 32768
the 2nd param in LOOKUP is processed: the SEARCH formula, which returns an array (based on the 'Descriptions' named range) of values which are #N/A (where no match found) & integer positions where matches are found.
the 3rd param in LOOKUP is processed: an array, it returns the array of 'Categories' (or 'SubCats').
the partially processed formula looks like this: LOOKUP( 32768, {"#N/A", "#N/A", 1, "#N/A", "#N/A"}, {"Fuel/Other", "Fuel/Other", "Food/Other", "Food/Other", "Food/Other"} )
The LOOKUP then finds the last integer found in the first array (1, the 3rd element, which represents "COLES" from 'Descriptions') & returns the string from the relevant position (3rd element) from the 2nd array ("Food/Other" for 'Categories' or "Coles" for 'SubCats'). All thanks to Barry Houdini.
None of the arrays in the excel LOOKUP & SEARCH formulas given use array formula's, just arrays.
However ... the only way I was able to get SEARCH to work in Calc with an array was by using an array formula (which I didn't find documented), which would not work for the formula (again, I could be wrong).
Hence why I posted the question here in the first place, to ask if SEARCH could take a cell range, also known as an 'array' (not an array formula).
Last edited by shell_l_d on Thu Mar 14, 2013 1:13 am, edited 1 time in total.
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
Re: [Solved] Can SEARCH function use a cell range?
ken johnson, thanks heaps, your formula's work (using array formulas & holding the ctrl key when filling down) in both Excel and Calc.
OpenOffice 3.4.1 on Windows 8 (64 bit)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)
(did have Office, but not anymore, have filed a counterfeit report with Microsoft)