excel - Querying a table that CONTAINS wildcards -

short version:

---

basically want this, in excel. instead of querying table using wildcards, want query table contains wildcards.

long version:

---

i'm creating spreadsheet summarize bank account transactions @ end of each month. want organise transactions in bank statement categories "groceries", "entertainment", "fuel" etc , sum total amount spent on each category.

i have sheet acts database, list of known account names each category (e.g. under "clothing" have names of accounts of clothing stores go to). have sheet first 2 columns containing transactions (account name, , amount), , column each category. copy each amount in column 2 correct category column using following formula:

=if(isna(match($b2,database!b:b,0)),"",$c2) 

where column b "account name" column bank statement, , column c contains amounts.

this works fine long data in database worksheet exact match. lot of account names similar e.g. "7elevenl12345", "7eleven836549" etc. how can add strings wildcards "7eleven*" database?

thanks in advance.

you can use search column b values in b2, although better restrict range i'll use rows 2 100

=if(isnumber(lookup(2^15,search(database!b$2:b$100,$b2))),$c2,"")

search automatically searches value within other text no wildcards required [you should remove wildcards database need "7eleven" etc.]. if 1 (or more) of searches match return number , lookup can test whether or not.

search function not case-sensitive, change find if want match case-sensitive

explanation:

when use

=search(database!b$2:b$100,$b2)

that returns "array" same size database!b$2:b$100. each value in database!b$2:b$100 either number (if specific value found within b2 it's position of start of value) or #value! error.

then when lookup "bignum" 2^15 in array, i.e.

=lookup(2^15,search(database!b$2:b$100,$b2))

that returns last number found in array....or #n/a if there no matches, using isnumber identifies whether there @ least 1 match or not.

if want see whole array returned by

=search(database!b$2:b$100,$b2)

then put in cell , select cell, press f2 select formula , f9 see whole array.

if have blanks in database!b$2:b$100 that's problem because blank "found" in value (at position 1) can edit formula prevent that, i.e.

=if(isnumber(lookup(2^15,search(database!b$2:b$100,$b2)*(database!b$2:b$100<>""))),$c2,"")

both versions of formula can shortened using count in place of lookup , isnumber, i.e. latter version can use

=if(count(search(database!b$2:b$100,$b2)*(database!b$2:b$100<>"")),$c2,"")

but version needs "array entry" - i.e. need confirm formula key combination ctrl+shift+enter such formula enclosed in curly braces { , }

note: 2^15 used here because guaranteed larger number number search function can return. 2^15 = 32768 maximum number of characters in cell 1 fewer - 32767