Partial Match Column A which has 14 Digits (match first 8 digits) to Column B (which have 8 digits)

[yousafalvi]

Hello,

I have an excel sheet where Column A contains less cells then Column B i.e Column A has 100 cells and Column B has 1500 Cells
and Column A has 14 digits data and B has 8 digits Data
i want to partial match that if Column A data is present in any cell of Column B (only first 8 digits) it highlights or give matched result in Column C

 

[DanteAmor]

Hi and welcome to MrExcel.
Try this:

varios 26may2021.xlsm
	A	B	C	D
1	Data 14 digits	Data 8 digits	Exists in A	Exists in B
2	12345678901234	55752965	0	1
3	90687386877509	34018766	0	0
4	54934028401432	59236476	0	0
5	49967248464170	12345678	1	0
6	86985123657616	76852268	0	1
7	93547965494186	97741157	0	0
8	31111820562978	61178388	0	0
9	94875945314053	86985123	1	1
10	82908598104959	62407312	0	1
11		81341973	0
12		21721837	0
13		82908598	1
14		65698237	0
15		29925019	0
16		23296868	0
17		51647810	0
18		37285122	0
19		94875945	1
20		29522400	0
Hoja7

Cell Formulas
Range	Formula
D2:D10	D2	=COUNTIF(B:B,LEFT(A2,8)+0)
C2:C20	C2	=SUMPRODUCT((LEFT($A$2:$A$100,8)=B2&"")*1)

 

[yousafalvi]

well it works fine also, but is there any more detailed way, because after this query i have to count the number of cells in Column A with the search result SUM to match all the 100 columns are found with the sum of search results, what if one cells f Column A8 first digits doesnt matched with Column B 8 digits , how to figure out that one cell

 

[DanteAmor]

I did not understand, you could show it with an example.

Use XL2BB tool

https://www.mrexcel.com/board/mrexcel-xl2bb.svg