Formula to return text based on what finds in another cell

richardjshaffer

Board Regular
Joined
Oct 9, 2008
Messages
84
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Richard

Check the following:
Excel Workbook
KLMN
1ReferenceCategorychq
234345 chq 443chqcash
343 eew cashcashvoucher
440403 33 chqchq
5voucher 4340voucher
685636 htr 978439#N/A
Sheet1
Excel 2002
Cell Formulas
RangeFormula
L2=LOOKUP(2^15,SEARCH($N$1:$N$3,K2),$N$1:$N$3)
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
734
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

Board Regular
Joined
Oct 9, 2008
Messages
84
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Board Regular
Joined
Oct 9, 2008
Messages
84
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,742
Messages
5,655,034
Members
418,171
Latest member
ramiroayala

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top