Calc: cell format issue with VLOOKUP Solved

Question

Hello,

With the help of a vertical search using 8 sheets linked to files, I created a summary table for which I used the formula:
=VLOOKUP(C2;mois.$A$19:$H$300;7;0)
where "C2" is the searched occurrence, "mois" is the (variable) name of the sheet, A19:A300 is the column where the list of occurrences is (these are names), 7 is the column number from which the value should be extracted
The problem is that if I keep the format of the occurrence (in Column C and in the A19/A300 area) as [name ID] it does not work (#N/A), but if I remove the ID from both places, the formula works correctly.
The alphanumeric ID is in parentheses (aaaa1234)
Note that the list in Column C was created from a special filter with "no duplicates" and "copy the result to" checked (in case that influences...)
I compared the names (in C and A) using the "Exact" command: calc indicates "true"
Thank you for helping or attempting to help me solve this problem

Configuration: Windows XP / Firefox 24.0/LibreOffice 4.1.5.3

gar_laban · Accepted Answer

At the same time, the answer was given to me on the Open Office forum: in Tools >>> Options >>> OpenOffice Calc >>> Calculate uncheck allow wildcard characters in formulas. ^By Phip It works!!

Vaucluse · Answer

Hello

to check the compliance of your list C, copy a code from A and paste it in its place in C to see if it works.

otherwise, please upload an example of your model on
https://www.cjoint.com/
because without that, it's hard to understand with your information, since you don't say what the complete code sought in A is.

looking forward to hearing from you

--
To err is human, to persist is diabolical.

yclik · Answer

Hello
Without a reference folder, it's difficult to follow

Perhaps by concatenating the fields Last Name, First Name, Employee Number
Place this concatenated field in the first column on the sheets where the search is performed and as a search criterion in the formula

Example:
If Last Name in B2, First Name in C2, Employee Number in D2
Concatenate in A2 with A2=B2&“ “&C2&“ “&D2

Be careful with homonyms.

gar_laban · Answer

First, thank you for the responses, and indeed it seemed to me that I was all... except clear...
@vaucluse: Already done but the error persists
@yclik first name last name and matriculation number are in the same cell...

So if I compare [first name last name] with [first name last name] it works, but if I do not change the cells and compare [first name last name matriculation number] with [first name last name matriculation number] it does not work....

But I am working on creating a model file, it will be simpler to understand...
Thanks again.

Vaucluse · Answer

The model won't be too much; it is indeed difficult to understand why the formula works with partial text but not with complete text, unless we deduce that the ID number and the name are not in the same cell?
A +

--
To err is human, to persist is diabolical.

gar_laban · Answer

Hello Here are the examples https://www.cjoint.com/?DHBi3cBH8Me

Vaucluse · Answer

Hello apparently the link goes to the file upload slide, but not to your template Best regards  -- To err is human, to persist is diabolical

gar_laban · Answer

It works for me... The file is called examples and it is zipped. I'm putting the link again just in case...  https://www.cjoint.com/?DHBi3cBH8Me  See you!

yclik · Answer

Hello
In principle, the parentheses disrupt the VLOOKUP function

One proposed solution is to remove the parentheses in the search with the formula to be placed in the 1st column of each sheet

=SUBSTITUTE((SUBSTITUTE(B2,"("; " "));")";"")

And the search formula becomes
in January 1st line
=VLOOKUP(A2;january.$A$19:$I$128;8;0)

gar_laban · Answer

Indeed, it's really the parentheses' fault...
and your formula to remove them works
The only downside is that my monthly sheets are linked to other files (and they need to remain linked)
So as soon as I refresh the links, the replacement formulas in the monthly sheets are deleted and it doesn't work anymore...
I will change my approach and see how I can build my list differently
Even if I have to create another topic....
In any case, thank you for the time spent
See you!

Calc: cell format issue with VLOOKUP

10 answers

Black screen on hp pc

Case fan not working

Username and password

How to delete quarantine files in defender?

Taskbar icon visibility

Texture issues in enshrouded

Activation of the esu program for updates on windows 10

Copy/paste scanned text jpeg format

Remove marker stain from pc screen

Automatic repair