Vlookup when Vehicle Number format is diffrent.

[Shabbo]

Dear Sir,
I have two Sheets, one is software entry and another one is manual entry.
I have put entry numbers from Manual sheet to software sheet.
Due to large data size I wanted to apply lookup.
Problem : the date and vehicle numbers are matching but it’s not working for helper column because Vehicle numbers format is different in both sheet.

 

[AlanSidman]

Using Power Query, here is the Mcode to reconfigure the data so that you can run a vlookup

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Vehicle No.", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Vehicle No.", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Vehicle No..1", "Vehicle No..2", "Vehicle No..3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Vehicle No..1", type text}, {"Vehicle No..2", type text}, {"Vehicle No..3", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Vehicle No..3", "Vehicle No..1", "Vehicle No..2"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Vehicle No..3", type text}}, "en-US"),{"Vehicle No..3", "Vehicle No..1", "Vehicle No..2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

 

[shrivallabha]

Formula solution would be to re-arrange the characters to match the sequence and do VLOOKUP. Below formula works with posted data.

=IFERROR(VLOOKUP(RIGHT(B2,4)&LEFT(B2,4)&MID(B2,6,2),'Software Report '!B:D,3,0),"")

I think these are vehicle number plates from Maharashtra state. जय महाराष्ट्र

 

[Juniad]

you can try this, it will ignore if vehicle no length is longer or shorter
=IFERROR(VLOOKUP(RIGHT(B2,LEN(B2)-SEARCH(" ",B2,SEARCH(" ",B2)+1))&LEFT(B2,SEARCH(" ",B2)-1)&MID(B2,SEARCH(" ",B2)+1,SEARCH(" ",B2,SEARCH(" ",B2)+1)-1-SEARCH(" ",B2)),'Software Report '!$B$2:$C$12,2,0),"")