Formula Help: Modify a currently working formula

[Johnny Thunder]

Hello All,

I have a formula that is working well but now that I have moved deeper into my project I noticed that there is one scenario that the formula currently doesn't account for and I was hoping someone might know a way to adapt a modification to my formula.

Current Formula: =IFERROR(IF(ISNUMBER(SEARCH("V",K2)),TRIM(MID(K2,SEARCH("V",K2),12)),TRIM(MID(L2,SEARCH("V",L2),12))),VLOOKUP(J2,A:B,2,0))

Formula Breakdown:
1. IF(ISNUMBER(SEARCH("V",K2)) - Explanation: If my range K2 contains "V" then Look at column K, if not Look at Column L
2. TRIM(MID(K2,SEARCH("V",K2),12)) - Explanation: Using Cell K2, Find the Location of the character "V" and use that as a starting position and extract 12 characters to the right
3. TRIM(MID(L2,SEARCH("V",L2),12))) - Explanation: If no "V" is found in K2, then Look in L2 for "V" and extract the 12 characters from this cell
4. VLOOKUP(J2,A:B,2,0)) - Explanation: This is part of the IFERROR Statement at the beginning of the formula, if neither Column K or L contain the letter "V" then lookup a value from a mapping table.

Modifcation needed (within the "ISNUMBER(SEARCH"): IF(K2 Contains "V" and the last two characters of the 12 needed end with "01" then extract, if no "V" is found in column K, then try the same thing in Column L.

So, its really just the validation piece of when the letter "V" is found, I need to validate that the last two character of the 12 character string end in "01" if not let the Vlookup do the work.

Sample String: P#6000346874, LINE 10, I/O#VKEOT0560101, KERR EHR - OTD
Result Needed: VKEOTO560101

New Scenario Sample: 01001 - OVERALL TERM DEAL
Result Needed: Use the vlookup because there is a "V" in the string but it does not end in "01" if you extracted 12 characters
Currently getting this: "VERALL TERM"

Any help is appreciated! ?

 

[mrshl9898]

=IFERROR(IF(ISNUMBER(SEARCH("V",K2)),IF(RIGHT(TRIM(MID(K2,SEARCH("V",K2),12)),2)="01",TRIM(MID(K2,SEARCH("V",K2),12)),IF(RIGHT(TRIM(MID(L2,SEARCH("V",L2),12)),2)="01",TRIM(MID(L2,SEARCH("V",L2),12))))),VLOOKUP(J2,A:B,2,0))

 

[Johnny Thunder]

Thank you for the help Marshal!

Unfortunately the formula is resulting in every row getting a "False" instead of the "Vxxxxxxxxxxx" string?

So for sure there are rows that will not have a "V" in either column which is when the Vlookup is key, but the formula is resulting in "False" on rows that clearly have the correct criteria to bring back a result.

I see what you are doing to achieve the additional parameter but its a huge formula so not sure if maybe there is a ")" in a wrong spot or some other issue causing the formula to get a "False"

 

[DanteAmor]

Try this

varios 10feb2020.xlsm
	A	B								J	K	L	M
1
2											P#6000346874, LINE 10, I/O#VKEOT0560101, KERR EHR - OTD		VKEOT0560101
3												P#6000346874, LINE 10, I/O#VKEOT0560101, KERR EHR - OTD	VKEOT0560101
4											01001 - OVERALL TERM DEAL		No Match
5										some			hi!
6
7
8	some	hi!
Hoja3

Cell Formulas
Range	Formula
M2:M5	M2	=IFERROR(IF(RIGHT(MID(K2,SEARCH("V",K2),12),2)="01",MID(K2,SEARCH("V",K2),12),#N/A),IFERROR(IF(RIGHT(MID(L2,SEARCH("V",L2),12),2)="01",MID(L2,SEARCH("V",L2),12),#N/A),IFERROR(VLOOKUP(J2,A:B,2,0),"No Match")))

 

[Johnny Thunder]

What's up Dante! Long time no see!

That revision did the trick! I appreciate both your help on this.

 

[DanteAmor]

What's up Dante! Long time no see!

If I can here I am to help.

Glad we could help & thanks for the feedback