Formula to return text based on what finds in another cell

[richardjshaffer]

Hi,

please can someone help with this query. I need to return in column B something to tell me the category of bank receipt based on what is found in the reference (column A). The type of receipt can appear anywhere in the reference:

<TABLE style="WIDTH: 133pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=177><COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 85pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=113>Reference</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Category</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>34345 chq 443</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">chq</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>43 eew cash</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">cash</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>40403 33 chq</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">chq</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>voucher 4340</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">voucher</TD></TR></TBODY></TABLE>

I understand the formula =IF(ISERROR(FIND("chq",A1)),"False","True") can help, and can see that I could do 3 of these formulas across columns B, C and D looking for all 3 types (chq, cash, voucher) and then use a concatenate, so that a unique string of TRUEFALSEFALSE would equal the relevant category. But is there a way of doing this all just in column B? Hope this is clear and you can help.
many thanks,
Richard

 

[Richard Schollar]

Hi Richard

Check the following:

Excel Workbook
	K	L	M	N
1	Reference	Category		chq
2	34345 chq 443	chq		cash
3	43 eew cash	cash		voucher
4	40403 33 chq	chq
5	voucher 4340	voucher
6	85636 htr 978439	#N/A
Sheet1

Excel 2002

Cell Formulas
Range	Formula
L2	=LOOKUP(2^15,SEARCH($N$1:$N$3,K2),$N$1:$N$3)

 

[JimM]

How about in B1

=IF(ISERROR(SEARCH("chq",A1)),IF(ISERROR(SEARCH("cash",A1)),IF(ISERROR(SEARCH("voucher",A1)),"no match","voucher"),"cash"),"chq")

 

[richardjshaffer]

thank you both, great solutions.

just wondering, how exactly the lookup formula works, what is the reasoning for having the 2 to a high power?
thanks again

 

[Richard Schollar]

Richard

Search returns the start position of the sought for values (chk, voucher, cash) in the cell under review. Since a cell can contain a maximum of 32,767 characters which is 2^15-1, a value of 2^15 simply ensures that the lookup value will be at least 1 greater than the maximum possible return value of the Search function. Because of the way Lookup works, the lookup value (2^15) will match against 32768 or a value lower than this and will then return the corresponding return value (chk,voucher,cash) dependent upon which of the returns from the Search function the lookup value matched against.

 

[richardjshaffer]

thanks again, think I've got it: it's looking for the next largest because it can't find 32768, and that is a value returned by the search formula when it finds eg chq in the chq reference.

 

[Richard Schollar]

yes - the benefit of the lookup approach over the nested if is it scales very well ie you could potentially do it with a hundred possible return values rather than 7 or 8 that you would be limited to with IF.