Searching for information and then returning that info.

[cbarryb]

Each day I get in a bank statement, and on there I need to search within the reference column for specific text in the form of invoice numbers. but to get around the fact the invoice numbers and letters are different, I want to make a table with these values in, but have wildcards in between the letters:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]S*IW[/TD]
[/TR]
[TR]
[TD]S*PW[/TD]
[/TR]
[TR]
[TD]U*IW[/TD]
[/TR]
</tbody>[/TABLE]

And so on.

I use this formula to return anything from the Reference column that starts with a 10000 number, and then it returns the number that I need:

=MID(B2,SEARCH("10000?????",B2),10)

I can change the "10000?????" to a cell number (R$2) and use wildcards in the cell, typing them in manually for each instance, but I was wondering if there is a way to use that formula to use a table of possible searches, being able to use wildcards (*) or (?) to return the 1st instance of an invoice number from the Reference column.

 

[footoo]

=if(mid(b2,2,5)="10000",mid(b2,2,10),"")

 

[footoo]

Or :

=IFERROR(MID(B2,SEARCH(10000,B2),10),"")

 

[cbarryb]

Or :

=IFERROR(MID(B2,SEARCH(10000,B2),10),"")

Thanks for you response, but what I need is to be able to change the 10000 part to the table NAME or $I:$I of possible searches, and then return the value of what any of those possibilities could be, i.e in my table I have:

[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Ref[/TD]
[TD]Amount[/TD]
[TD]Acc No[/TD]
[TD]Ledger[/TD]
[TD][/TD]
[TD]Acc[/TD]
[TD]Inv No[/TD]
[TD]S*IW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/01/2019[/TD]
[TD]SN1234IW 100001234[/TD]
[TD]$100[/TD]
[TD]100001234[/TD]
[TD]ME[/TD]
[TD][/TD]
[TD]100001234[/TD]
[TD]SN1234IW[/TD]
[TD]S*IV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S*PW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U*IW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U*IV[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U*PW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want the formula in H2 to look at B2, using the table in column I for search terms, and then return the full result, starting with say S and ending with IW (SN1234IW) from B2.