[Solved] VLOOKUP() does not reconize text with parenthesis

[Ferdski]

why does vlookup returns text with parenthesis as #N/A... is there a fix on this? I use many parenthesis on text references.

[Villeroy]

Turn off regular expression in the options. Each option in "menu:Tools>Options...Calc>Calculate" is stored with the current document only.

[Ferdski]

Thank you for the answer... Is there any other option than turning off the regular expression? because in the same document I use wild card references which needs the regular expressions enabled.

[Robert Tucker]

Have you tried preceding the parenthesis with a backslash \ in the formula as Villeroy wrote in the other thread. (Sorry, I didn't point this out there – I wasn't sure whether back-references were available in Calc or used in formulas.)

[Ferdski]

Could not use the backslash \ for the formula as seen on the attached image.

It seems that the only option is to remove the parentheses which are in thousands .

Is there any other option?

[Robert Tucker]

Are you not able to disable both regular expressions and "Operators = and <> match whole cell" as suggested in the other thread (when, of course, there's no need for the backslashes)?

[Ferdski]

Are you not able to disable both regular expressions and "Operators = and <> match whole cell" as suggested in the other thread (when, of course, there's no need for the backslashes)?

Yes able to do that but then the sumif will come out blank as seen on the first picture.

[Robert Tucker]

but then the sumif will come out blank as seen on the first picture.

Not if you use:

=SUMIF(B1:B9;"Subtotal";D1:D9)

(With "Operators = and <> match whole cell" unchecked, you can just match to the part of the text string in the cell you are interested in, without wildcards)

[Ferdski]

but then the sumif will come out blank as seen on the first picture.

Not if you use:

=SUMIF(B1:B9;"Subtotal";D1:D9)

(With "Operators = and <> match whole cell" unchecked, you can just match to the part of the text string in the cell you are interested in, without wildcards)

sumif.jpg

vlookup.jpg

It resulted to Err:510...

[Robert Tucker]

Can you paste out the formula you used? Obviously in the example you gave previously there is no problem.

[Ferdski]

Can you paste out the formula you used? Obviously in the example you gave previously there is no problem.

No need... I tried the formula on a simple grid and it works... I'm in a process of debugging and looking for the error on my 7000rows x 35column x 7 sheets spreadsheet

[Ferdski]

Now I have another problem... the vlookup does not give the exact reference which resulted to the error. It would display the amount with the same first word it encounters on the table.

[Robert Tucker]

Perhaps time to start a new thread with a little more clarification of the (new) problem.

[Ferdski]

Yes your right... see new thread on multiple formula