| View previous topic :: View next topic |
| Author |
Message |
Xelebes3
Power User
Joined: 30 Nov 2008
Posts: 50
Location: Canada
|
 Posted: Sun Nov 30, 2008 6:03 pm Post subject: [solved] Match-test Function? |
 |
|
Maybe the wrong name but here goes.
I want to look up a range in any column or row within that range for any matching cell.
Example:
Is "ADD" in this range:
| Code: | COW | BIRD | HOUSE
DOG | COOT | STOOL
EAT | FOXY | TRUTH |
?
The answer I want is false.
Any elegant solution?
Last edited by Xelebes3 on Mon Dec 01, 2008 1:28 pm; edited 1 time in total |
|
| Back to top |
|
 |
bobban
OOo Enthusiast
Joined: 02 Jan 2008
Posts: 172
Location: Australia
|
| Posted: Sun Nov 30, 2008 6:39 pm Post subject: |
|
|
If you data is in A1:C3, and your 'search criteria' (ADD, do no put quotes in the search criteria cell, although you can put the search string directly in the formula in which case you do put the quotes on) is in D1.
=IF(COUNTIF(A1:C3;D1);"TRUE";"FALSE")
This search is not case sensitive. I'm not sure but there may be a way to stipulate that.
_________________
nil sine labore |
|
| Back to top |
|
|
Xelebes3
Power User
Joined: 30 Nov 2008
Posts: 50
Location: Canada
|
| Posted: Sun Nov 30, 2008 6:46 pm Post subject: |
|
|
Well that was unobvious. =/
Works like a charm. |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sun Nov 30, 2008 7:34 pm Post subject: |
|
|
| Xelebes3 wrote: | Well that was unobvious. =/
Works like a charm. |
Just a little more obvious perhaps would be to have
=IF(COUNTIF(A1:C3;D1)<>0;"TRUE";"FALSE")
Zero is the default value.
David. |
|
| Back to top |
|
|
Xelebes3
Power User
Joined: 30 Nov 2008
Posts: 50
Location: Canada
|
| Posted: Sun Nov 30, 2008 8:16 pm Post subject: |
|
|
Well... I don't know. Just seems unobvious to have to use COUNTIFs.
Edit - ok, now it's clicking in my head as to why this works. Now I'm smacking my forehead. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Dec 01, 2008 4:22 am Post subject: |
|
|
=ISNUMBER(MATCH(D1; $A$1:$C$3;0))
Spreadsheets can be seen as a programming language for non-programmers. Once you learn the fundamental facts of spreadsheets a whole universe of possibilities unfolds before your eyes. In the next phase you'll try to do literally everything in spreadsheets before you notice that programming can be so much easier without spreadsheets ...
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Mon Dec 01, 2008 7:08 am Post subject: |
|
|
| Villeroy wrote: | =ISNUMBER(MATCH(D1; $A$1:$C$3;0))
Spreadsheets can be seen as a programming language for non-programmers. Once you learn the fundamental facts of spreadsheets a whole universe of possibilities unfolds before your eyes. In the next phase you'll try to do literally everything in spreadsheets before you notice that programming can be so much easier without spreadsheets ... |
Even then, it's easier to let someone else do the programming, and simply be a user. 
David. |
|
| Back to top |
|
|
Xelebes3
Power User
Joined: 30 Nov 2008
Posts: 50
Location: Canada
|
| Posted: Mon Dec 01, 2008 9:25 am Post subject: |
|
|
| Hah, the only thing I've ever programmed was music in Q-Basic. It was the only thing I could figure out. Mind you, I was 7 or 8 years old back then and never looked at another programming language since then apart from other music related codes. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Dec 01, 2008 10:37 am Post subject: |
|
|
| Quote: | | Hah, the only thing I've ever programmed was music in Q-Basic |
So you know the difference between number and text, and you know about functions taking numbers and text as mandatory or optional arguments. Calc knows nothing but text and numbers, the latter may be formatted to represent a date/time or boolean. Functions may yield error values, which can be seen as third data type. Knowing the difference between content (number, text) and format (different appearance of the very same values) is one of the most frequent issues in this forum.
The other thing you need to know is absolute and relative referencing in spreadsheets.
Adding charts, annotations, pictures, sorting, filtering, ..., all that is secondary. The core functionality is about feeding functions with relative and absolute references to numbers and text.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Mon Dec 01, 2008 12:48 pm Post subject: |
|
|
| Xelebes3 wrote: | | Hah, the only thing I've ever programmed was music in Q-Basic. |
Unfortunately, the spreadsheet does demand some level of programming logic. If not up to it, you must face the task and take a course. We [as least myself] have spent not hours or days, or even years, but decades, and are still know only a little of the full capabilities.
P.S. I also play classical piano since childhood, and am here to tell you that any new study can be a struggle ...but one worthwhile when mastered. |
|
| Back to top |
|
|
Xelebes3
Power User
Joined: 30 Nov 2008
Posts: 50
Location: Canada
|
| Posted: Mon Dec 01, 2008 1:22 pm Post subject: |
|
|
I took a course at NAIT as part of the business program but right now I'm taking a hiatus from school due to medical reasons and now I have to kill time. I'm deciding to spend it on spreadsheets. Maybe I'll work on databases next.
For programming music, I've used a variety of softwares and trackers, so if it makes a sound, I'm familiar to what it does. Spreadsheets are familiar to me but not the more hardcore innerworks. Databases I've only just learned how to even start building one. But if it makes sound, I hone in.
@ Villeroy:
I couldn't make heads or tails what you were talking about in that tutorial. |
|
| Back to top |
|
|
|
