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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
675
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,460
Messages
5,511,505
Members
408,853
Latest member
JoshuaHudsonpTi45

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top