## conditional matching in an array

### conditional matching in an array

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

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

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: conditional matching in an array

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

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: conditional matching in an array

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

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: conditional matching in an array

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

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

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: conditional matching in an array

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

Villeroy
Volunteer

Posts: 28844
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany