[Solved] Lookup for multiple values

Discuss the spreadsheet application

[Solved] Lookup for multiple values

Postby bdf1215 » Tue Jan 14, 2020 8:07 pm

Hello,

I am trying to find any of a table of values as a partial match to a single cell. For instance, in the following single cell:

AA BB CC

I would want to search several terms:

BB
FF
GG
LL
SS
ZZ


So, I'd want a desired result as follows:

AA BB CC 1

DD EE FF GG 1

HH II JJ 0

KK LL MM 1


I've tried Vlookup scenarios, searches, matches, but I can't seem to find the right mix to lookup a partial match from a cell from an entire table of options. I've attached a sample file too.

Sorry if this has been answered, but I could only find searches for single elements per cell, not multiple elements per cell.
Attachments
Partial Match Table Lookup.ods
Desired results
(11.23 KiB) Downloaded 19 times
Last edited by bdf1215 on Tue Jan 14, 2020 10:56 pm, edited 1 time in total.
Openoffice 4.1.6 on Windows 7, 10
bdf1215
 
Posts: 5
Joined: Tue Dec 31, 2019 2:52 am

Re: Lookup for multiple values

Postby Villeroy » Tue Jan 14, 2020 8:47 pm

lookup_substrings.odb
(12.58 KiB) Downloaded 23 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lookup for multiple values

Postby bdf1215 » Tue Jan 14, 2020 8:50 pm

Thanks Villeroy. So no way to handle this in Excel cleanly?
Openoffice 4.1.6 on Windows 7, 10
bdf1215
 
Posts: 5
Joined: Tue Dec 31, 2019 2:52 am

Re: Lookup for multiple values

Postby mikele » Tue Jan 14, 2020 10:04 pm

Hello,
take a look at this.
A special case is D11! The SEARCH()-function finds "Co." as well as "Corp" in "IJKL Corp." because "Co." is interpreted as an regular expression.
Attachments
Partial Match Table Lookup.ods
(12.01 KiB) Downloaded 32 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 59
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Lookup for multiple values

Postby bdf1215 » Tue Jan 14, 2020 10:16 pm

That's perfect! For my purposes, I'm really only interested in 0's and non 0's, so the special case can stay. I'm going to spend some time deconstructing what you have there to make sure I understand it.

You are awesome!
Openoffice 4.1.6 on Windows 7, 10
bdf1215
 
Posts: 5
Joined: Tue Dec 31, 2019 2:52 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests