[Solved] Array Formula Using Index, Match & Reg Expressions

[Mal F]

I am a new LibreOffice (4.2) user and in the process of converting my Excel files to Calc.
It has been going well but I have struck a problem that I have summarised in the attached Calc worksheet. (I have used the columns from my much larger worksheet in my example).

Basically I import names from a “First External Source” (Column “BL”), convert them to Last Name “IR”, First Name “IS” and match them to a “Second External Source”, Last Name “IT”, First Name “IU” to get the Indexed Value in “IV”.
The result is shown in Column “II” which currently shows “N/A” but should be the Indexed Value from “IV”.
e.g. M. White in BL5 should return -4.71 in II5.

I have no access to change anything in the First External Source but I can access the Second External Source although I would be reluctant to make any changes there.

I have broken the formula in II2:II7 down into smaller parts as shown in BL11:BM17.
The formula is an Array Formula and I have enabled Regular Expressions.

Have I made an error in the syntax or do I have a problem with Regular Expressions?

Any help or suggestions would be appreciated.

Thanks,
Mal

[ken johnson]

The regex seems not to be working, so maybe just assume first name is always initial...

Code: Select all

IF(BL2="";0;-INDEX($IV$2:$IV$7;MATCH(IR2&" "&LEFT(IS2;1);$IT$2:$IT$7&" "&LEFT($IU$2:$IU$7;1);0)))

Ken Johnson

[acknak]

I'm not so sure the regex is the source of the problem. I think it may have something to do with using an expression as the search array for MATCH. That's the part I've not seen before, and I'm not sure how it's supposed to work.

If I break things out into separate columns and use regular formulas instead of array formulas, then everything works--no errors at least.

Edit: PS:

No, I spoke too soon. Playing around a bit more, it seems that it's the combination of the regex and the expression search array. The regex works on a normal range and the MATCH works with a plain text search key but the regex does not work with the range expression.

[Mal F]

Thanks for your replies.
Both options obviously work so I will work out the best for my circumstances.
You help is much appreciated.

Mal

[acknak]

Ok, there is actually a bug report for this:
Issue 117393: Implement regular expressions on arrays. You can register there and add your vote (up to two) or comment.

[Mal F]

Thanks for that acknak.
This confirms what we suspected.

Mal