LOOKUP(9.99999999999999E+307,SEARCH( With Additional Conditions

[schmallegger]

I hope that someone has an elegant solution to solve my "situation", as follows:

Via keywords, I make a pre-qualification of ledger entries. This works very well via the following statement in a ledger table:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Keywords[Keyword],[@Description]),Keywords[Account]),"Missing Keyword")

However, and now comes the spice:

I would like to add additional criteria for the search-range of the command above based on the data structure of the Keywords table, as I have several conditions:

Keyword Condition Amount Type Condition Amount Condition Account Type Condition Account No. Condition Company Account Type Account
ThisIsMyKeyword Negative Bank Account ACC0 Private Bank Account ACC1
ThisIsMyKeyword Positive Bank Account ACC0 Private Bank Account ACC2
ThisIsMyKeyword +123 Bank Account ACC0 Private Bank Account ACC3
ThisIsMyKeyword -123 Bank Account ACC0 Private Bank Account ACC4
ThisIsMyKeyword Bank Account ACC0 Private Bank Account ACC5
ThisIsMyKeyword Negative Bank Account ACC0 Live Bank Account ACC6
ThisIsMyKeyword Positive Bank Account ACC0 Live Bank Account ACC7
ThisIsMyKeyword +123 Bank Account ACC0 Live Bank Account ACC8
ThisIsMyKeyword -123 Bank Account ACC0 Live Bank Account ACC9
ThisIsMyKeyword Bank Account ACC0 Live Bank Account ACC10
ThisIsMyKeyword Bank Account ACC0 Live Bank Account ACC11
ThisIsMyKeyword Bank Account ACC12


These additional criteria I know of course from my ledger table and are changing dynamically.

I tried things like "=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Keywords[Condition Account Type]&Keywords[Condition Account No.]&Keywords[Keyword],[@Description]),"Bank Account"&[Source]&Keywords[Keyword]),"Missing Keyword")" but this won't work. Does anyone have an idea, what I am doing wrong and/or come up with a smart solution?

Thanks in advance!