Lookup with wildcards (sequential lookup)

[Noeleke1301]

Hi all,

I'm trying to solve the following problem. I have a main table with values in each column. I want to look up an Id from another table.

Unit	Debtor	Location	Id
A	ABCD	AMS	(should be 14)
B	ABCD	QUE	(should be 12)
C	ABCD	AMS	(should be 14)

The other table contains Id's with condition columns. However, some of these values are blank, which mean they act as a wildcard/fallback.

Id	Debtor	Location	Amount
12	ABCD		30
14	ABCD	AMS	40

If I use an OR operator || , like main[location] = lookup[location] || ISBLANK( main[location] ), it always returns Id 14 because it will always find a match with the blank [location], and thus returns Id 14.

How can I make this check sequential? So only check the next condition if it doesn't find an exact match first? So in this case, first check the whole lookup table if there's a specific row with [location] AMS, if not then check for blank [location]

[lbendlin · Accepted solution]

Id =

var d = [Debtor]

var l = [Location]

var f = filter(Table2,[Debtor]=d && coalesce([Location],l)=l)

return if(countrows(f)>0,maxx(f,[Id]))