execute a different formula basis cell value

[PrashanthKumar123]

hi - I am working on a text string project.

Few sample keywords include -

IMPS OUTWARD

PURCHASE SUBJECT

INTERCITY ECS

In the below table, col A has few sample entries in my input data. Col B recognizes the string based on the above keywords. My next step is to execute a formula based on Col B cell values, i.e., a different formula for "IMPS OUTWARD", another for "PURCHASE SUBJECT and another for "INTERCITY ECS". Presently, I have a nested if to do this, but as my list of keywords increases, I worry if should look at a better way.

IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI	IMPS OUTWARD	Formula for IMPS Outward
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI	IMPS OUTWARD	Formula for IMPS Outward
IMPS OUTWARD ORG UPI To umakumpatla70@okhdfcbank,REF NO - 024817061778, UPI	IMPS OUTWARD	Formula for IMPS Outward
PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: XXXXXXXXXX 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551	PURCHASE SUBJECT	Formula for Purchase Subject
IMPS OUTWARD ORG UPI To qXXXX0411@ybl,REF NO - 024820419752, UPI	IMPS OUTWARD	Formula for IMPS Outward
INTERCITY ECS. PAID NACH:Paid to HDFCLTD:HDFC01651000012196:HDFC BANK LTD Refno:262549161 -HDFC BANK	INTERCITY ECS	Formula for Intercity ECS

 

[Toadstool]

Hi PrashantKumar1,

You can't interpret formula from cell contents without using VBA. If you can put the formulae as CHOOSE options then this would work:

PrashanthKumar1.xlsx
	A	B	C	D	E	F	G	H
1	IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI	IMPS OUTWARD	2				String	Formula Number
2	IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI	IMPS OUTWARD	2				IMPS OUTWARD	1
3	IMPS OUTWARD ORG UPI To umakumpatla70@okhdfcbank,REF NO - 024817061778, UPI	IMPS OUTWARD	2				PURCHASE SUBJECT	2
4	PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: XXXXXXXXXX 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551	PURCHASE SUBJECT	4				INTERCITY ECS	3
5	IMPS OUTWARD ORG UPI To qXXXX0411@ybl,REF NO - 024820419752, UPI	IMPS OUTWARD	2
6	INTERCITY ECS. PAID NACH:Paid to HDFCLTD:HDFC01651000012196:HDFC BANK LTD Refno:262549161 -HDFC BANK	INTERCITY ECS	6
7
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=IFERROR(CHOOSE(INDEX($H$2:$H$99,MATCH(B1,$G$2:$G$99,0)),1+1,2+2,3+3),"")

 

[PrashanthKumar123]

hi Toadstool, thank you for the help! Formula 1, 2 and 3 are not static in my case, but actually a search formula on corresponding row element in Col A. Would CHOOSE work here?

If no, could you also help share what the VBA response might look like?

thanks in advance!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[PrashanthKumar123]

hi Fluff, I'm using 2016

 

[Fluff]

In that case if you create a list of your values you can use choose like

+Fluff 1.xlsm
	A	B	C	D	E
1	IMPS OUTWARD		IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI	IMPS OUTWARD	imps formula
2	PURCHASE SUBJECT		IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI	IMPS OUTWARD	imps formula
3	INTERCITY ECS		IMPS OUTWARD ORG UPI To umakumpatla70@okhdfcbank,REF NO - 024817061778, UPI	IMPS OUTWARD	imps formula
4			PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: XXXXXXXXXX 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551	PURCHASE SUBJECT	purchase formula
5			IMPS OUTWARD ORG UPI To qXXXX0411@ybl,REF NO - 024820419752, UPI	IMPS OUTWARD	imps formula
6			INTERCITY ECS. PAID NACH:Paid to HDFCLTD:HDFC01651000012196:HDFC BANK LTD Refno:262549161 -HDFC BANK	INTERCITY ECS	inter formula
7
List2

Cell Formulas
Range	Formula
E1:E6	E1	=CHOOSE(MATCH(D1,$A$1:$A$3,0),"imps formula","purchase formula","inter formula")

Just replace the "imps formula" etc, with your actual formulae.

 

[Toadstool]

hi Toadstool, thank you for the help! Formula 1, 2 and 3 are not static in my case, but actually a search formula on corresponding row element in Col A. Would CHOOSE work here?

If no, could you also help share what the VBA response might look like?

thanks in advance!

Yes, CHOOSE can use different formulae and not just static entries. e.g. here I use three different formulae to extract the reference numbers

PrashanthKumar2.xlsx
	A	B	C	D	E	F	G	H
1	IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI	IMPS OUTWARD	024519820714				String	Formula Number
2	IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI	IMPS OUTWARD	024620963921				IMPS OUTWARD	1
3	IMPS OUTWARD ORG UPI To umakumpatla70@okhdfcbank,REF NO - 024817061778, UPI	IMPS OUTWARD	024817061778				PURCHASE SUBJECT	2
4	PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: XXXXXXXXXX 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551	PURCHASE SUBJECT	024616394551				INTERCITY ECS	3
5	IMPS OUTWARD ORG UPI To qXXXX0411@ybl,REF NO - 024820419752, UPI	IMPS OUTWARD	024820419752
6	INTERCITY ECS. PAID NACH:Paid to HDFCLTD:HDFC01651000012196:HDFC BANK LTD Refno:262549161 -HDFC BANK	INTERCITY ECS	262549161
7
Sheet1 (2)

Cell Formulas
Range	Formula
C1:C6	C1	=IFERROR(CHOOSE(INDEX($H$2:$H$99,MATCH(B1,$G$2:$G$99,0)),SUBSTITUTE(RIGHT(A1,LEN(A1)-SEARCH("REF NO -",A1)-8),", UPI",""),RIGHT(A1,LEN(A1)-SEARCH("Ref: ",A1)-4),MID(A1,SEARCH("Refno:",A1)+6,(SEARCH(" ",A1,SEARCH("Refno:",A1)+6))-(SEARCH("Refno:",A1)+6)+1)),"")

If using VBA you may look at the Evaluate function.

 

[PrashanthKumar123]

hi fluff and toadstool, thanks a ton for your assistance. I tried the following steps and found partial success but struggle with at one step. Can you help check and tell me how I can fix?

Step 1: This is a sample of my keyword and formula table. There are more keywords and formula combinations, but this is to illustrate.

Keywords	Formula
IMPS OUTWARD	IF(SUMPRODUCT(--ISNUMBER(SEARCH(SMB_Keywords,Table1[@Description])))>0, TRIM(RIGHT(SUBSTITUTE(Table1[@Description],",",REPT(" ",100)),100)),MID(Table1[@Description],SEARCH("ORG UPI TO",Table1[@Description])+11,SEARCH(",",Table1[@Description])-SEARCH("ORG UPI TO",Table1[@Description])-11))
PURCHASE SUBJECT	MID(table1[@Description],SEARCH("4857",table1[@Description])+20,SEARCH("Ref",table1[@Description])-SEARCH("4857",table1[@Description])-20)
INTERCITY ECS	MID(Table1[@Description],SEARCH("Paid to",Table1[@Description])+8, SEARCH("Refno",Table1[@Description])-SEARCH("Paid to",Table1[@Description])-8)

Table 1 is the table that I created for my input data. Description is the column.

Step 2: I also created a name range with =EVALUATE(VLOOKUP(Table1[@[Payment Type]],Definitions!$A$1:$C$7,3,0)). This works well. Essentially, this looks up the keyword (Col 2 in the below table) and executes the corresponding formula from the table in Step 1.

Description	Keyword	Expected Outcome	Actual Outcome
IMPS OUTWARD ORG UPI To XXXXXXXX6607@icic0000183.ifsc.npci,REF NO - 024519820714, UPI	IMPS OUTWARD	XXXXXXXX6607@icic0000183.ifsc.npci	#VALUE!​
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1015tmy4w7ksb2r@paytm,REF NO - 024620963921, UPI	IMPS OUTWARD	UPI	#VALUE!​
IMPS OUTWARD ORG UPI To umakumpatla70@okhdfcbank,REF NO - 024817061778, UPI	IMPS OUTWARD	umakumpatla70@okhdfcbank	#VALUE!​
PURCHASE SUBJECT: MCUPOS 02SEP1156 Card no.: 5125XXX2XXXX4857 02SEP20 115640 Innovative Retail Conc\BIGBASK Ref: 024616394551	PURCHASE SUBJECT	Innovative Retail Conc\BIGBASK	Innovative Retail Conc\BIGBASK
IMPS OUTWARD ORG UPI To qXXXX0411@ybl,REF NO - 024820419752, UPI	IMPS OUTWARD	UPI	#VALUE!​
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXX1011xf41l1xin30@paytm,REF NO - 024820436772, UPI	IMPS OUTWARD	UPI	#VALUE!​
IMPS OUTWARD ORG UPI To paytmqrXXXXXXXXXX0118edlmk3ewmx@paytm,REF NO - 024820599339, UPI	IMPS OUTWARD	UPI	#VALUE!​
INTERCITY ECS. PAID NACH:Paid to HDFCLTD:HDFC01651000012196:HDFC BANK LTD Refno:262549161 -HDFC BANK	INTERCITY ECS	HDFCLTD:HDFC01651000012196:HDFC BANK LTD	HDFCLTD:HDFC01651000012196:HDFC BANK LTD

Col 3 is the expected outcome. Interestingly, this works well for keywords PURCHASE SUBJECT and INTERCITY ECS. But for IMPS OUTWARD, I am unable to figure what the issue is?

Do you see what I might be doing wrong?

 

[Fluff]

What is SMB_Keywords?
The reason the formula doesn't work is that it's more than 255 characters in length

 

[PrashanthKumar123]

Genius! that fixed the issue!

thanks to you and Toadstool, I have two ways of doing this