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.

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

<colgroup><col><col></colgroup><tbody>
</tbody>

We have another excel file that basically looks like this:

ADMNGE XYZ Company
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.

<tbody>
</tbody>

 

[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?

=LOOKUP(99^99,SEARCH($C$1:$C$5,"*"&A1&"*"),$D$1:$D$5)

<tbody>
</tbody>

M1RE76 NAB ACF MLCI WMP EQ	C	K123456	A
CBNY-M1SAGE MLCI WMP 28	E	Z123456	B
		M1RE76	C
		G123457	D
		M1SAGE	E

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>