VLOOKUP where the the Lookup Value is longer than the value it searches

[CaptainCsaba]

Hey Everyone!

So here is the thing. We get a report that we have to use and every row contains a 6 digit code and there is random gibberish before and after it and it is never the same. We have a list of these codes with what they mean and I want to create a VLOOKUP macro for it. The problem is as you can see is that we can't search only by the code because of the gibberish. An example:

M1RE76 NAB ACF MLCI WMP EQ 76
CBNY-M1SAGE MLCI WMP EQ 38

What we have in our excel is: M1RE76 and M1SAGE (not all beginwith M1. Some begin with EQ or WM so there is no real logic to them)

There are around 300 of these and what we need in VLOOKUP is "M1RE76" and "M1SAGE". They are never in the same place in the report we get. For now they all only begin with letters but the can end in numbers. I don't really know how I can exact the codes and the use VLOOKUP to make our job much easier. What makes it harder is that not all the report is made up of these, so for example we get 100 rows and around 15 are like this.

 

[Gerald Higgins]

Hi, welcome to the board.

This is do-able IF you can come up with some logic for accurately identifying the required codes.
If, as you say, "there is no real logic to them" then it's going to be impossible.

Remember - there doesn't have to be only one rule - there might be several rules that we can work with - for example, all are 6 digits long, may begin with M1 or EQ or WM, and so on.

 

[FormR]

Hi, welcome from me also!

Is it something like this?

Excel 2013/2016
	A	B	C	D	E
1	Code	Formula		Lookup	Return
2	M1RE76 NAB ACF MLCI WMP EQ 76	C		K123456	A
3	CBNY-M1SAGE MLCI WMP EQ 38	E		Z123456	B
4				M1RE76	C
5				G123457	D
6				M1SAGE	E
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(1,-SEARCH($D$2:$D$6,A2),$E$2:$E$6)

 

[CaptainCsaba]

It is more like this:

This a part of what we get, as you can see, the required codes are sorrounded by other text and besides this we get many other things that don't have this code.

[TABLE="width: 346"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]CBNY-T.A. WLD EX U.S CR EQ PRT DFA[TABLE="width: 346"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]2,944,191[/TD]
[TD]CBNY-INT CR EQ PRT DFA INV DM GR IN[/TD]
[/TR]
[TR]
[TD]346,401[/TD]
[TD]CBNY-INT LARGE CAP GR PT OF DFAIDG[/TD]
[/TR]
[TR]
[TD]143,823[/TD]
[TD]CBNY-INT SOC CORE EQ PORT/DFAIDG[/TD]
[/TR]
[TR]
[TD]43,714[/TD]
[TD]CBNY-INTL HIGH REL PROF DFA INV[/TD]
[/TR]
[TR]
[TD]225,324[/TD]
[TD]CBNY-INTL SUSTAINABILITY 1 PORTFOL[/TD]
[/TR]
[TR]
[TD]2,398[/TD]
[TD]CBNY-JCF GBL ADAP CAP PRES FD[/TD]
[/TR]
[TR]
[TD]669[/TD]
[TD]CBNY-JCF GLB ADAP MLT ASSET FD[/TD]
[/TR]
[TR]
[TD]309,582[/TD]
[TD]CBNY-JHF II INTL SEA FD[/TD]
[/TR]
[TR]
[TD]123,778[/TD]
[TD]CBNY-JOHN HANCOCK FUNDS[/TD]
[/TR]
[TR]
[TD]669,467[/TD]
[TD]CBNY-LEGAL AND GEN COLL INV TRST[/TD]
[/TR]
[TR]
[TD]284,730[/TD]
[TD]CBNY-ADMNGE MLCI WMP EQ 75[/TD]
[/TR]
[TR]
[TD]75,665[/TD]
[TD]CBNY-M1GSTR NAB ACF JANA MMGST[/TD]
[/TR]
[TR]
[TD]76,203[/TD]
[TD]CBNY-BLKGLF MLCI WMP EQ 74[/TD]
[/TR]
[TR]
[TD]60,484[/TD]
[TD]CBNY-ALALGE MLCI WMP EQ 76[/TD]
[/TR]
[TR]
[TD]31,118[/TD]
[TD]CBNY-M1REGD NAB ACF MLCI WMP EQ 2[/TD]
[/TR]
[TR]
[TD]652,430[/TD]
[TD]CBNY-CXBIGE CBU WUK EQ 18[/TD]
[/TR]
[TR]
[TD]68,131[/TD]
[TD]CBNY-CBMSGE MLCI WMP EQ 38[/TD]
[/TR]
[TR]
[TD]219,177[/TD]
[TD]CBNY-M1SSJP MLCI JANA PASSV[/TD]
[/TR]
[TR]
[TD]351,760[/TD]
[TD]CBNY-NATIONAL FINANCIAL SERVICES LL[/TD]
[/TR]
[TR]
[TD]99,778[/TD]
[TD]CBNY-SDA INTL EQUITY INDEX FUND[/TD]
[/TR]
[TR]
[TD]1,129,550[/TD]
[TD]CBNY-SIEFORE BANAMEX BASICA 3[/TD]
[/TR]
[TR]
[TD]1,227,790[/TD]
[TD]CBNY-SIEFORE BANAMEX BASICA 4[/TD]
[/TR]
[TR]
[TD]982,215[/TD]
[TD]CBNY-SIEFOREBANAMEXBASICA2 SADECV[/TD]
[/TR]
[TR]
[TD]2,528[/TD]
[TD]CBNY-STIFEL NICOLAUS SEG ACCT EBOC[/TD]
[/TR]
[TR]
[TD]517,943[/TD]
[TD]CBNY-T.A. WLD EX U.S CR EQ PRT DFA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

We have another excel file that basically looks like this:

[TABLE="width: 346"]
<tbody>[TR]
[TD]ADMNGE XYZ Company[/TD]
[/TR]
[TR]
[TD]M1GSTR ABC Company
BLKGLF KLF Company

So we get that confusing report and somebody had already researched what the codes mean we want the company names to appear in the report excel inn the next colums after VLOOKup looks it up it the other excel file.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

It is more like this:

Hi, that looks principally the same - did you try and adapt the suggestion to your layout?

 

[Aladin Akyurek]

Care to specify what the output must be when we look for say M1RE76?

 

[Mike Szczesny]

Can you use this? [TABLE="width: 75"]
<tbody>[TR]
[TD="width: 75"] =LOOKUP(99^99,SEARCH($C$1:$C$5,"*"&A1&"*"),$D$1:$D$5)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 439"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]M1RE76 NAB ACF MLCI WMP EQ[/TD]
[TD]C[/TD]
[TD]K123456[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]CBNY-M1SAGE MLCI WMP 28[/TD]
[TD]E[/TD]
[TD]Z123456[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M1RE76[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]G123457[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]M1SAGE[/TD]
[TD]E[/TD]
[/TR]
</tbody>[/TABLE]