VLook-Up Match first 3 characters of one cell with another column

[Kulsumbi]

Serial #	Result		Townid	MyUniq
123566			123	1111
123456			276	2222
276455			456	3333
276456			789	4444
456778			000	5555

I want to match first 3 letters of "serial #" against "town ID's" if it matches then "myuniq id" must fill in the result cell.


Please assist

 

[sandy666]

like this?

Serial #	Result	Townid	MyUniq
123566	1111	123	1111
123456		276	2222
276455		456	3333
276456		789	4444
456778		000	5555

 

[Kulsumbi]

like this?

Serial #	Result	Townid	MyUniq
123566	1111	123	1111
123456		276	2222
276455		456	3333
276456		789	4444
456778		000	5555

Yes buddy

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeText = Table.TransformColumnTypes(Source,{{"Serial #", type text}, {"Townid", type text}}),
    Result = Table.AddColumn(TypeText, "Result", each if Text.Start([#"Serial #"],3) = [Townid] then [MyUniq] else null),
    Reorder = Table.ReorderColumns(Result,{"Serial #", "Result", "Townid", "MyUniq"})
in
    Reorder

Serial #	Townid	MyUniq		Serial #	Result	Townid	MyUniq
123566	123	1111		123566	1111	123	1111
123456	276	2222		123456		276	2222
276455	456	3333		276455		456	3333
276456	789	4444		276456		789	4444
456778	000	5555		456778		000	5555

or (with your example)

=IF(LEFT(A2,3)=C2,D2,"")

 

[sandy666]

or

Book1
	L	M	N	O
3	Serial #	Result	Townid	MyUniq
4	123566	1111	123	1111
5	123456	1111	276	2222
6	276455	2222	456	3333
7	276456	2222	789	4444
8	456778	3333	000	5555
Sheet3

Cell Formulas
Range	Formula
M4:M8	M4	=INDEX($O$4:$O$8,MATCH(--LEFT(L4,3),$N$4:$N$8,0))

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"Serial #", type text}, {"Townid", type text}}),
    Three = Table.AddColumn(Text, "Custom", each Text.Start([#"Serial #"],3)),
    Join = Table.NestedJoin(Three,{"Townid"},Three,{"Custom"},"Table",JoinKind.Inner),
    Expand = Table.ExpandTableColumn(Join, "Table", {"MyUniq"}, {"MyUniq.1"}),
    RC = Table.RemoveColumns(Expand,{"Custom"}),
    Reorder = Table.ReorderColumns(RC,{"Serial #", "MyUniq", "Townid", "MyUniq.1"}),
    Ren = Table.RenameColumns(Reorder,{{"MyUniq", "Result"}, {"MyUniq.1", "MyUniq"}})
in
    Ren

Serial #	Result	Townid	MyUniq
123566	1111	123	1111
123566	1111	123	2222
123456	2222	276	3333
123456	2222	276	4444
276455	3333	456	5555

 

[Kulsumbi]

or

Book1
	L	M	N	O
3	Serial #	Result	Townid	MyUniq
4	123566	1111	123	1111
5	123456	1111	276	2222
6	276455	2222	456	3333
7	276456	2222	789	4444
8	456778	3333	000	5555
Sheet3

Cell Formulas
Range	Formula
M4:M8	M4	=INDEX($O$4:$O$8,MATCH(--LEFT(L4,3),$N$4:$N$8,0))

Thanks.. ??

 

[Kulsumbi]

Works

or

Book1
	L	M	N	O
3	Serial #	Result	Townid	MyUniq
4	123566	1111	123	1111
5	123456	1111	276	2222
6	276455	2222	456	3333
7	276456	2222	789	4444
8	456778	3333	000	5555
Sheet3

Cell Formulas
Range	Formula
M4:M8	M4	=INDEX($O$4:$O$8,MATCH(--LEFT(L4,3),$N$4:$N$8,0))

Works fine for the above table..

However it's shows N/A for the below table where serial number starts with 002.

Serial	result	town id	my id
002123123	N/A	003	9999

 

[sandy666]

Book1
	L	M	N	O
3	Serial #	Result	Townid	MyUniq
4	123566	1111	123	1111
5	123456	1111	276	2222
6	276455	2222	456	3333
7	276456	2222	789	4444
8	456778	3333	002	5555
9	002678	5555	542	6666
Sheet3

Cell Formulas
Range	Formula
M4:M9	M4	=INDEX($O$4:$O$8,MATCH(LEFT(L4,3),BASE($N$4:$N$8,10,3),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kulsumbi]

Thanks

Book1
	L	M	N	O
3	Serial #	Result	Townid	MyUniq
4	123566	1111	123	1111
5	123456	1111	276	2222
6	276455	2222	456	3333
7	276456	2222	789	4444
8	456778	3333	002	5555
9	002678	5555	542	6666
Sheet3

Cell Formulas
Range	Formula
M4:M9	M4	=INDEX($O$4:$O$8,MATCH(LEFT(L4,3),BASE($N$4:$N$8,10,3),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks ??