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:

S*IW

S*PW

U*IW

<tbody>
</tbody>

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:

A	B	C	D	E	F	G	H	I	J	K
Date	Ref	Amount	Acc No	Ledger		Acc	Inv No	S*IW
01/01/2019	SN1234IW 100001234	$100	100001234	ME		100001234	SN1234IW	S*IV
								S*PW
								U*IW
								U*IV
								U*PW

<tbody>
</tbody>

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.