Check if any cell in range matches end of string in cell

English
#1

Hi,

This is a bit tricky: I have strings taken from column A and normally do a VLOOKUP with this string against a matrix to get specific values. But this time, I need to check if the end of that string matches any string from column B and get the cell from column B (e.g. B3) that matched the criterion in order to use it as base of a VLOOKUP. The search should be case insensitive.

I was thinking about using Regex but I don’t know if I can add a range where the search condition is always a cell of that range with $ added at the end, so it would work as Regex delimiter for end of string like: hinweis$, symbol$ (from example below).

The formula for this is written in another column of the same line as the cell in column A and gets propagated to every cell in that column, so if there is a string in that line of column A, it will start the search.

An example would be:

cells in column A:

1 Spondylose
2 Cookie-Symbol
3 Betriebsystem
4 Emissionshandelssystem
5 Polarkoordinatensystem
6 Kinderrückhaltesystem

cells in column B:

1 koeffizient
2 fahrzeug
3 hinweis
4 mittel
5 symbol
6 system
7 weite
8 wesen
9 zelle

‘system’ from B6 would match for the last 4 cells in A thus result for each of these cells would be B6, result for A2 would be B5.
After that I would use the respective cell from B as reference for a VLOOKUP on the next columns

MATCH would not work, as the search string is any string from a range and the place where the range would be set is only one cell.

Best regards,
Pascal

1 Like
#2

ask134650.ods (22.9 KB)

#3

Hi Volleroiy,

what is Match whole cell in German? On the other side, I cannot even find it as reference on screenshots for English version…

Do you mean: Search criteria = and < > must apply to whole cells

But I now see, that in your example you use column C as starting point but it must be column A as all cells from A must be checked against C. (well, kind of as it’s some kind of reverse search)

I already found the solution you gave, but it does not give me the result I need. It only checks if there is a match in A, but I need to know whether the end string of A matches any of these strings in C and if so, which cell in column C matches A.

EDIT: Maybe MATCH could be of use. Is there a way to list all cells (e.g. via ADDRESS) that are matched by a MATCH function and then check if the cell we want to look up (e.g. A1249) is within that list of cells. If so, then use the cell from C that triggered that match, as starting point for a VLOOKUP?

#4

“Suchkriterien = und <> müssen auf ganze Zellen zutreffen”.

ask134650_2.ods (18.6 KB)

#5

Thanks. So ‘Search criteria = and < > must apply to whole cells’ is the ‘Match whole cell’ everyone refers to. Could it be this was renamed in the past?

Still the problem, that this version only gets the first match in A but I⁠ need to get all matches in A. If we manage to get all matches as a list (array) in D, then I can get a solution by using a VLOOKUP on D.

So, can we get a list of ALL matches for that string in C?

EDIT: Just a minor correction to your MATCH:
From
=INDEX($A$1:$A$6;(MATCH(".*"&C5&"$";$A$1:$A$6;0)))
to
=INDEX($A$1:$A$6;(MATCH(C5&"$";$A$1:$A$6;0)))

the “."& is not needed before the word as Regex interpretes C5&"$" as ".”&C5&"$" but shorter is better in terms of performance, especially for large files.

#6

I was sloppy. “MatchWholeCell” is the API name of this option.
https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1SpreadsheetDocumentSettings.html#abdcdc4f8ef7f88eb1cf06f031ee73abf

#7

Well you are not the only one to use the wrong name :wink: It’s used all over the net.

#8

You need a filter. A spreadsheet filter hides non-matching rows. Like 99% of today’s spreadsheet users, you ask for database functionality. Typical spreadsheet formulas do not return subsets of lists (record sets in database terms). Excel tries very hard to do these stunts. so far, LibreOffice can not really do the same in a reliable manner.

#9

Is there any plan to implement QUERY function in CALC (As Google sheet)

#10

We have a database component for free. It can even query spreadsheet ranges.

#11

Suggestion for a spreadsheet filter, so called “advanced filter”.
ask134650_3.ods (18.3 KB)

#12

The problem is, that I don’t need it to filter (to visualize them) but to get results in order to use these within other functions until I get the correct result from a VLOOKUP in this case. ^^ It’s part of a very large replacement and/or conditional (if then) project.

Else it would be too easy.

That’s also why it would be easier if I could just use A in a VLOOKUP but it does not match the whole string from C, thus will not work and I can’t find any way to apply a Regex on the Search matrix of the VLOOKUP, else I’d just have something like: C:C&"$" for the matrix

#13

Unfortunately a visualisation and not really userfriendly for a list of a few thousand terms to check automatically. ^^

BTW: in Regex you don’t need the .* before the string you are looking for if you use $. mystring$ automatically checks if a string ends on mystring.

so you can use: G6&"$ (shorter and better performance with larger files)
instead of: .*"&G6&"$

But: if you would not use $ at the end, then you would need it, in order to also get everything before mystring.

#14

It is as “user friendly” as a spreadsheet can be. You select a search criterion from the validation listbox at B1 and refresh the filtered database range at A13. A little macro may do the refresh automatically.
IMHO, it is completely impossible to develop “user friendly” solutions for untrained “end users” on spreadsheets. Every spreadsheet user needs to be proficient with this versatile tool.
First draft of a user-friendly database solution:
ask134650.odb (17.4 KB)

#15

it’s not meant for end users. ^^ It’s to help me with a specific task, where everything should be automated so that the functions per line run against matrix in C as soon as there is a string inserted into a new cell in A.
The problem is that the strings in C are not of same length, else it would be very easy (e.g. if they would all be 6 chars long: =VLOOKUP(RIGHT(A123;6);C:D;2)
But due to them being of variable length, I would need the search matrix C:D to be compatible with Regex of some type, either by being able to append &"$" to it or by using a new column where I have the string from B with $ at the end, but this would mean I can apply Regex to C:D like =REGEX(C:D;A123)

#16

But this way you still have no formula returning the full record set of matching records.
=VLOOKUP(RIGHT(A123;6);C:D;2;0) (in database mode you need the last argument zero) returns the first match only, which is what you actually asked for in the first place ("Check if any cell range matches end of string) and which I solved in my first answer.

#18

yes, if I use a VLOOKUP from A against C:D, then it’s the first one I need as I order C from longest to shortest.
But your first answer did not solve the problem as the length is variable and not always 6. :wink:
The VLOOKUP I provided in my last answer was just to show what would work, if I knew the length beforehand. As it’s unknown, I’d revert to some REGEX as it has some functionality to work with variable length but REGEX does not work with a range/matrix.

Meanwhile I found some kind of workaround and it’s not a really neat way of doing it and must be adapted the longer the strings in C get, but at least it works and generates the results I need:

  • I added a column to check the length of cells in C and order the matrix from longest to shortest via that new column.
  • then I set up this patchwork function where I iterate through all possible lengths:

=IFERROR(VLOOKUP(RIGHT(A20;11);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;10);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;9);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;8);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;7);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;6);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;5);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;4);$Tabelle2.A:D;4;0);IFERROR(VLOOKUP(RIGHT(A20;3);$Tabelle2.A:D;4;0);RIGHT(A20;LEN(A20)-1))))))))))

#21

Lo Calc 24.8 adds support for several functions that might be relevant, such as FILTER(), SORT(), SORTBY(), among others.

https://wiki.documentfoundation.org/ReleaseNotes/24.8#New_functions

#25

:hot_face:

=IFERROR(VLOOKUP(REGEX(LOWER(A20);".*?("&TEXTJOIN(")|(";1;$Tabelle2.A:A)&")";;1);$Tabelle2.A:D;4;0);"")

of course better with case-insensitive-regex as provided by @Lupp ⇓

=IFERROR(VLOOKUP(REGEX(A20;"(?i).*?("&TEXTJOIN(")|(";1;$Tabelle2.A:A)&")";;1);$Tabelle2.A:D;4;0);"")
#31

Hi Karolus,

the only problem is that this version, despite being neater and shorter slows down my file extremely. Up to now the file has around 1k words to be checked against a tab of 24 words and just copying the function to around 20 rows makes the file unresponsive for around 2 minutes with every tab change or change to any related cell on both tabs. :frowning: I⁠ tried to extend it to a few more rows and it takes even longer.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.