OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[solved] Match-test Function?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Sun Nov 30, 2008 6:03 pm    Post subject: [solved] Match-test Function? Reply with quote

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
View user's profile Send private message
bobban
OOo Enthusiast
OOo Enthusiast


Joined: 02 Jan 2008
Posts: 172
Location: Australia

PostPosted: Sun Nov 30, 2008 6:39 pm    Post subject: Reply with quote

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
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Sun Nov 30, 2008 6:46 pm    Post subject: Reply with quote

Well that was unobvious. =/

Works like a charm.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Sun Nov 30, 2008 7:34 pm    Post subject: Reply with quote

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
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Sun Nov 30, 2008 8:16 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 01, 2008 4:22 am    Post subject: Reply with quote

=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 https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Dec 01, 2008 7:08 am    Post subject: Reply with quote

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. Wink

David.
Back to top
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Mon Dec 01, 2008 9:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 01, 2008 10:37 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Dec 01, 2008 12:48 pm    Post subject: Reply with quote

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
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Mon Dec 01, 2008 1:22 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group