Lookup partial values and if match return partial value from another cell

Something like this:

=LEFT(INDEX('Workbook 2'!F2:$F$8,MATCH('Expected Result'!A2,'Workbook 2'!$E$2:$E$8,0)),2)

Try

H2

Formula:

=SUBSTITUTE(INDEX('Workbook 2'!C2:C8,MATCH('Workbook 1'!E2,'Workbook 2'!D2:D8,0)),ROUND(NPV(-0.9,IFERROR(MID(E2,100-ROW(1:100),1)/10,"")),0),"")+0

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or

Formula:

=LEFT(INDEX('Workbook 2'!$C$2:$C$8,MATCH(E2,'Workbook 2'!$D$2:$D$8,0)),2)+0

Originally Posted by Renejorgensen

@Olly yes you are right on what I'm trying to achieve. Sadly I don't have Power Query but looking at your attached result it seems to do what I'm trying to achieve.

Power Query is a free addin for Excel 2013: https://www.microsoft.com/en-gb/down....aspx?id=39379

I strongly recommend installing it, and learning how to use it.

In your example, you can use a straightforward match, as Ali posted.

But from your description, I'm guessing that your real data has non identical cells, which you want to match based only on first word, number and last word.

If my understanding is correct, then both these values should return the same prefix from Workbook 2:

Stockholm cc 8131 Central Projects
Stockholm City Centre 8131 Suburban Projects

So we look up Stockholm | 8131 | Projects, and return prefix 40. Is that correct?

If that logic is sound... then you can do this quite easily using Power Query.

fnRemoveAllText:

Formula:

(input) =>

let

    CharsToRemove = List.Transform({32..45,47,58..126}, each Character.FromNumber(_)),

    Removed = Text.Remove(input,CharsToRemove)

in

    Removed

Table1: (load this to your output table)

Formula:

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Removed Prefix" = Table.RemoveColumns(Source,{"Prefix"}),

    #"Add CCfirst" = Table.AddColumn(#"Removed Prefix", "CCfirst", each Text.BeforeDelimiter([CC], " ", 0), type text),

    #"Add CCnumber" = Table.AddColumn(#"Add CCfirst", "CCnumber", each fnRemoveAllText([CC])),

    #"Add CClast" = Table.AddColumn(#"Add CCnumber", "CClast", each Text.AfterDelimiter([CC], " ", {0, RelativePosition.FromEnd}), type text),

    #"Merged Queries" = Table.NestedJoin(#"Add CClast",{"CCfirst", "CCnumber", "CClast"},Table2,{"CCfirst", "CCnumber", "CClast"},"NewColumn",JoinKind.LeftOuter),

    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"CCprefix"}, {"Prefix"}),

    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"CCfirst", "CCnumber", "CClast"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"From-datum", type date}, {"Prefix", Int64.Type}}),

    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Id", Order.Ascending}})

in

    #"Sorted Rows"

Table2: (load to connection only)

Formula:

let

    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    #"Add CCfirst" = Table.AddColumn(Source, "CCfirst", each Text.BeforeDelimiter([Name], " ", 0), type text),

    #"Add CCnumber" = Table.AddColumn(#"Add CCfirst", "CCnumber", each fnRemoveAllText([Name])),

    #"Add CClast" = Table.AddColumn(#"Add CCnumber", "CClast", each Text.AfterDelimiter([Name], " ", {0, RelativePosition.FromEnd}), type text),

    #"Add CCprefix" = Table.AddColumn(#"Add CClast", "CCprefix", each Text.Replace(Number.ToText([Code]),[CCnumber],""))

in

    #"Add CCprefix"

Now you can simply refresh the query to update your output table.


Worked example attached.