OR function

DipSta · Newbie Joined: 08 Jan 2009 Posts: 2

Hi,
im pretty new to using spreadsheets for more than basic tables. What im trying to do is search for a persons name in 1 of 4 columns. Using the following code obtained from a tutorial: =INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.E5:E44;0);2+4) I can obtain the row and the data I want. The problem is I have specified the location E5:E44, when its possible for result to be in A5:A44, E5:E44, I5:I44 or M5:M44. What I would like is to be able to search all 4 ranges "at once". I have tried to use: =OR(INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.A5:A44;0);2);INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.E5:E44;0);2+4)) but this returns #N/A , and yes I know one has 2 and the other has 2+4, that required. Any help would be great.

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

The OR function needs things that are either TRUE or FALSE, so if your INDEX functions give something else, the OR will not work.

It will be clumsy but maybe use:

=IF(ISERROR(INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.A5:A44;0);2));INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.E5:E44;0);2+4);INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.A5:A44;0);2))

It will get worse when trying to check more columns.

Maybe if you explain what you are trying to achieve with the INDEX and MATCH, there might be a simpler solution.

DipSta · Newbie Joined: 08 Jan 2009 Posts: 2

Okay,on 1 sheet I have 4 columns with players names in them. so A has players name then BCD have votes, E has names FGH have votes etc. on a Second sheet i have all players names in 1 column A and votes in BCD. When i enter a votes on the first sheet i want the second sheet to update its votes, so if a vote is in B then it will appear ont he second sheet in B. (1st sheet is round by round, second sheet will hold all votes unformatted). The match is to find the players name in one of the 4 columns containing names and then use index to fill in the votes, player name plus 1 cell for 1st vote upto p+3 for 3rd vote.
The problem i am having is that i can make it search through 1 column and work but each round a players name may change columns so i need to be able to make sheet 2 search all 4 possible columns to find the match then index the votes from it.
I have tried nested IF's with the following code: IF(MATCH(T(A6);TeamSheet_rd9.A5:A44;0);INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.A5:A44;0);2);IF(MATCH(T(A6);TeamSheet_rd9.E5:E44;0);INDEX(TeamSheet_rd9.A5:Q44;MATCH(T(A6);TeamSheet_rd9.E5:E44;0);2+4)))
but that returns #N/A

Villeroy · Posted: Mon Jan 12, 2009 4:40 am Post subject:

Simply test which of the MATCHes returns #NA and if it should really return a number. Matching text-values involves several tricky options such as case-sensitivity and others. See check boxes in menu:Tools>Options...Calc>Calculation.

It pays off to split calculation models into as many steps as possible.
X1: =MATCH(...)
Y1: =INDEX(...MATCH(...)
Z1: IF(ISNA(Y1);...;...)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org