Vlookup help

[Harini1100]

Hello Guys,

I need help on vlookup formula, below is the 2 different source file from different system. Normal vlookup is not working since the data is having missing or additional characters. Kindly help,

 

[awoohaw]

Hello Guys,

I need help on vlookup formula, below is the 2 different source file from different system. Normal vlookup is not working since the data is having missing or additional characters. Kindly help,

View attachment 108226

What values are you looking to find in what column? Are you trying to find the values of Source File 2 as partial matches in Source File 1?

And what version of excel are you doing this in?

 

[awoohaw]

Instead of using VLOOKUP, use the below Match in conjunctions with INDEX, or using XLOOKUP. But depends on your excel version:

Book1
	A	B	C	D
1	Source 1	Source 2	Location of Source 2 in Source 1 (Non 365)	Location of Source 2 in Source 1 (365)
2	1-888-OHIOCOMP	ALA CARTE	ALA CARTE (NO KNOWN AS BRIMAR)	ALA CARTE (NO KNOWN AS BRIMAR)
3	1199SEIU Family of Funds	AKAL SECURITY/COASTAL	AKAL SECURITY/COASTAL INTL SECURITY	AKAL SECURITY/COASTAL INTL SECURITY
4	6 Degrees Health	6 Degrees	6 Degrees Health	6 Degrees Health
5	AKAL SECURITY/COASTAL INTL SECURITY	1-888-OHIO	1-888-OHIOCOMP	1-888-OHIOCOMP
6	ALA CARTE (NO KNOWN AS BRIMAR)	1199SEIU	1199SEIU Family of Funds	1199SEIU Family of Funds
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX($A$2:$A$6,MATCH(B2&"*",$A$2:$A$6,0))
D2:D6	D2	=XLOOKUP(B2&"*",$A$2:$A$6,$A$2:$A$6,"not found",2,1)

 

[Harini1100]

I want to map the source 1 data with source 2 to bring the values to source 1 data. They are 2 different files
Considered that I have first file data in column A and Second file data in Column B, now I want to get the result of partial match from second file to first file.
Could you please share the formula for this?

 

[awoohaw]

How is someone to know to what level of matching you want? Do you want first 10 characters to match?
But this seems to work... pay attention to my comment about being certain you get the results you want:

and this is for 365, not sure there is a way in other versions.

Book1
	A	B	C
1	Source 1	Source 2	Matching Source 1 to Source 2, Please test it thouroughly, it works here, but may not work everytime.
2	1-888-OHIOCOMP	ALA CARTE	1-888-OHIO
3	1199SEIU Family of Funds	AKAL SECURITY/COASTAL	1199SEIU
4	6 Degrees Health	6 Degrees	6 Degrees
5	AKAL SECURITY/COASTAL INTL SECURITY	1-888-OHIO	AKAL SECURITY/COASTAL
6	ALA CARTE (NO KNOWN AS BRIMAR)	1199SEIU	ALA CARTE
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=XLOOKUP(A2,$B$2:$B$6,$B$2:$B$6,"",-1,1)

 

[Harini1100]

Yes you are right it doesn't work when I apply for wide cell range, any way thanks for the help.

 

[Alex Blakenburg]

Give this a try:

=XLOOKUP(1,--ISNUMBER(SEARCH($B$2:$B$6,A2)),$B$2:$B$6,"")