Lookup match that contains value

[Ratatosk]

I've created a Virtual column in my PlantLibrary table with the following formula:

LOOKUP(Lower([_THISROW].[Botanical name]),"LatinDictionary", "Latin","Norwegian")

This works for full matches.

What I want is:

If the [Botanical name] is "Acer Palmatum", and the word in the table "LatinDictionary" column [Latin] is "acer", I'd like to show the translation of "acer" that is in the "LatinDictionary" column [Norwegian].

[Suvrutt_Gurjar · Accepted solution]

If understanding of your requiremet is correct, you could try below.

LOOKUP() is anyway a SELECT() function wrapped in a specific format. 

So instead of LOOKUP() you could try a SELECT() function based expression something like below

ANY(SELECT(LatinDictionary[Norwegian], CONTAINS([_THISROW].[Botonical Name],[Latin] )))

[Ratatosk]

Wow. This works like magic!. How come [Latin] does not have to be LatinDictionary[Latin]?

[Suvrutt_Gurjar]

If the column is from the same table as where SELECT() is executed, then you could simply name the column. Of course in certain case [_THISROW] qualifier could be required  before the column , even  in the same table to include the context of a specific row.