Ask Your Question

How to create a type of Procv but find result with the text and just with initials letters of the text?

asked 2018-11-05 21:24:09 +0100

jacktorresr gravatar image

Example: I need to find somes names on a big table, with a lot data on it, but I don't know the full name of the person, just the beginning of, like, a guy who has the name "Jeffrey Campbell River" and I just know the surname "Jef" how can I create a function on LibreOffice Calc for find the full name and the others data's about him?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-11-07 16:15:51 +0100

erAck gravatar image

The VLOOKUP() (I assume that's what in your UI translation is Procv()) can contain wildcards (OR regular expressions, depending on the setting under Tools -> Options -> Calc -> Calculate, General Calculations) so with wildcards you could use

=VLOOKUP( "Jef*" ; datarange ; index_of_data_field_to_return ; 0 )

The 4th argument 0 is necessary for an exact match on unsorted data. Note that this finds the first match, you'll have to make sure the result actually matches your expectations (eg. whether it's "Jeff Bonzo" or "Jeffrey Campbell River").

edit flag offensive delete link more


Thank you for the explanation, but, can I do with a variable instead? Like, capturing the value of G4 instead?

jacktorresr gravatar imagejacktorresr ( 2018-11-08 12:11:00 +0100 )edit

Forget it, I made here. Thank you so much.

jacktorresr gravatar imagejacktorresr ( 2018-11-08 14:37:47 +0100 )edit

Of course, using a cell reference instead of the literal string is possible.

Marking the answer as correct by clicking on the check mark would be appreciated. Thanks.

erAck gravatar imageerAck ( 2018-11-09 18:11:25 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-05 21:24:09 +0100

Seen: 32 times

Last updated: Nov 07 '18