conditional matching in an array

Discuss the spreadsheet application

conditional matching in an array

Postby nsteika » Tue Jan 22, 2008 1:30 am

I have trouble writing a formula in Calc for something I can do quite easily in Excel.
basic problem: i have a list of names in column A. i need to find the next row with an identical name in column A, that also has a value > 0 in column B.
In excel i write something like: {=MATCH(A2;IF(B3:B$150>0;A3:A$150);0)} and it works nicely.
eg:

row | A:name | B:criteria | formula | result
1 | Jim | 2 | {=MATCH(A1;IF(B2:B$6>0;A3:A$6);0)} | 5
2 | Kyle | 1 | {=MATCH(A2;IF(B3:B$6>0;A3:A$6);0)} | 3
3 | Jim | 0 | {=MATCH(A3;IF(B4:B$6>0;A4:A$6);0)} | 3
4 | Stan | 1 | {=MATCH(A4;IF(B5:B$6>0;A5:A$6);0)} | na
5 | kyle | 2 | {=MATCH(A5;IF(B6:B$6>0;A6:A$6);0)} | na
6 | Jim | 2 | na | na

this formula does not work in Calc. I can't figure out why. Any ideas? thanks.
nsteika
 
Posts: 2
Joined: Tue Jan 22, 2008 1:04 am

Re: conditional matching in an array

Postby acknak » Tue Jan 22, 2008 3:09 am

I think the reason it doesn't work in Calc is because IF() cannot produce a cell range (or array) as a result.

As far as I know, there is no succinct way to do this in Calc. One approach is to create a "helper" column with the combined key: A2 & "," & B2, then a simple MATCH on that will work.

Maybe someone else has a way to do it all in the formula.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: conditional matching in an array

Postby Villeroy » Tue Jan 22, 2008 6:13 am

X1:X6: {=IF($B$1:$B$6;$A$1:$A$6;"")}
Y1: =MATCH($A1;$X2:X$6;0)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: conditional matching in an array

Postby acknak » Tue Jan 22, 2008 7:28 am

Very nice!

It still requires a helper column, but it's very close to what was working in Excel.

It also seems to disprove my suggestion that IF() is the problem. Instead, it seems that MATCH() is the one that won't accept an array in place of the cell range.

E.g. { IF($B$2:$B$7>0; $B$2:$B$7) } --> 2, 1, FALSE, 1, 2, 2
and { SUM( IF($B$2:$B$7>0; $B$2:$B$7) ) } --> 8

And { =IF($B$2:$B$7>0; $A$2:$A$7;"") } --> Jim, Kyle, '', Stan, kyle, Jim
but { =MATCH("Jim"; IF($B$2:$B$7>0; $A$2:$A$7; ""); 0) } --> Err:504
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: conditional matching in an array

Postby nsteika » Tue Jan 22, 2008 4:09 pm

thanks! i'll use the helper column idea for now.

So the MATCH function doesn't work with arrays, just cell ranges. Is there a function in Calc that will find the position of a match in an array? i tried this with some of the various lookup ones without much success. Just curious.
nsteika
 
Posts: 2
Joined: Tue Jan 22, 2008 1:04 am

Re: conditional matching in an array

Postby acknak » Tue Jan 22, 2008 4:33 pm

I think match() is all there is.

It might be worth filing an issue. I think this is a known problem in general--that many functions in Calc don't work with arrays as they should--but it wouldn't hurt to file a specific request.

Just make up a simple example that works in Excel and doesn't work in Calc, and submit it: Open Office Quality Assurance - Report Bugs
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: conditional matching in an array

Postby Villeroy » Tue Jan 22, 2008 7:07 pm

Match works in array context.
A1:B6: {=RAND(0;9)}
Match each A in B:
{=MATCH($A$1:$A$6;$B$1:$B$6;0)}

Excel is extra smart when it guesses that you want to match a single value in an array returned by an array context. =MATCH(cell;IF(range;range);0)
Oh, I'm pretty shure the might be some formula without helper column, but I'm too lazy yet.
Mumble, mumble,... Match, Offset, Row, ...Mumble, mumble,...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Villeroy and 35 guests