Excel, query: How to add specific info from query 2 into query 1 with lookup

I want to add data from the second query (Data) to the first query (Overview) depending on the information in the first query. I'll show my question with pictures to make it more clear. I have these two queries:

I want to add the "ITEM" information from the Data query to overview query looking up the "NAME.1" Column. I think I'm close to the solution because I can lookup the row with this formula:

=Data{[NAME.1=[NAME.1]]}

What do I need to add to the formula to only show the column "ITEM"? (and/or to show column number 0 in this case. Both solutions would be nice to understand it better how it works)

---

The data used in my screenshots:

Overview

Data

---

---

Some extra information in reply to the answer of Darren below

When using merge, is it also possible to to merge the ITEM information if NAME.1=NAME.1 and if there is no NAME.1 match, to look if there is a match in NAME.2=NAME.2? Possibly with the result in the same column.

Overview

Data

---

---

Some extra information in reply to the answer of Ron below

When I use =try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]) and my tables look like this:

Overview

Data

Then I will get the wrong information in the table:

The green highlighted is correct, the 3 yellow ones are wrong and should be null. I think I should add some kind of if null=null give null or something in that direction?

---

---

Some extra information in reply to the suggestion of Ron below

When I do merge I get this code:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME", "NAME.2", "NUMBER", "RANDOM"}, {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Content", "AddInfoFromTable", each try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NAME.1"}, Data, {"NAME.1"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"ITEM"}, {"ITEM"})
in
    #"Expanded Data"

And modify the code in "Advanced Editor" to this, it still shows no error

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Table1")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"NAME", "NAME.2", "NUMBER", "RANDOM"}, {"NAME.1", "NAME.2", "NUMBER", "RANDOM"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Content", "AddInfoFromTable", each try Data{[NAME.1=[NAME.1]]}[ITEM] otherwise Data{[NAME.2=[NAME.2]]}[ITEM]),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"NAME.1","NAME.2"}, Data, {"NAME.1","NAME.2"}, "Data", JoinKind.LeftOuter),
    #"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"ITEM"}, {"ITEM"})
in
    #"Expanded Data"`

However, I'm not sure what to do with these values in your suggestions:

{"ITEM", each [ITEM]{0}, Int64.Type})
in
    Join

In my example the data in ITEM is numbers, in reality it contains a combination of numbers and letters (e.g., 0AB123 or AB123, etc.)

I believe the most efficient method is using the Table.Join function as demonstrated in another answer:

let

//Read in the two tables
    SourceOverview = Excel.CurrentWorkbook(){[Name="Overview"]}[Content],
    Overview = Table.TransformColumnTypes(SourceOverview,{{"NAME", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type text}}),

    SourceData = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Data = Table.TransformColumnTypes(SourceData,{{"NAME", type text}, {"ITEM", Int64.Type}, {"Y/N", type text}}),

//Join the two tables using Name as common element
//and extract the ITEM column
    Joined = Table.TransformColumns(
        Table.NestedJoin(Overview,"NAME",Data,"NAME","ITEM", JoinKind.RightOuter),
        {"ITEM", each [ITEM]{0}, Int64.Type}),
    #"Sorted Rows" = Table.Sort(Joined,{{"NAME", Order.Ascending}})
    
in
    #"Sorted Rows"

However, your method can also work the way you want by merely adding the Field Name to your formula:

Data{[NAME=[NAME]]}[ITEM]

Your posted data for this problem:
Overview

Data

Results

Edit: Given your additional information of having to compare Name.1 or Name.2, you can modify the Table.Join function as follows:

Please note that this method requires that NAME.1 or NAME.2 be null. If that is not the case, then coding would be more complex

   Join = Table.TransformColumns(    
        Table.NestedJoin(Overview,{"NAME.1","NAME.2"},Data2,{"NAME.1","NAME.2"},"ITEM",JoinKind.FullOuter),
        {"ITEM", each [ITEM]{0}, Int64.Type})
in
    Join

Edit2: Now with further information that either table might or might not have Names in none, one or both NAME.1 and NAME.2 cells, the following shows two different techniques of managing that situation which probably gives the results you want

let
    Source = Excel.CurrentWorkbook(){[Name="Overview3"]}[Content],
    #"Overview" = Table.TransformColumnTypes(Source,{
        {"NAME.1", type text}, {"NAME.2", type text}, {"NUMBER", Int64.Type}, {"RANDOM", type text}}),
  
//Add index column to be able to go back to original table order
    #"Added Index" = Table.AddIndexColumn(Overview, "Index", 0, 1, Int64.Type),

//Create a Name column that we will expand to all names in a single column
    #"Added Custom" = Table.AddColumn(#"Added Index", "Name", each List.RemoveNulls({[NAME.1]} & {[NAME.2]})),
    #"Expanded Name" = Table.ExpandListColumn(#"Added Custom", "Name"),

//Since Data table could also have Names in both NAME columns, we can unpivot (and delete the unneeded columns)
    #"Unpivot Data" = Table.UnpivotOtherColumns(Table.RemoveColumns(Data3,"Y/N"),{"ITEM"},"Attribute","Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivot Data",{"Attribute"}),

//Now we can merge the tables and return the ITEM
    #"Add Item" = Table.TransformColumns(
        Table.NestedJoin(#"Expanded Name","Name",#"Removed Columns","Name","ITEM",JoinKind.LeftOuter),
        {"ITEM", each try [ITEM]{0} otherwise null, type text}),
    #"Removed Columns1" = Table.RemoveColumns(#"Add Item",{"Name"}),

//Group by Index to return initial table in order
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Index"}, {

    //If two names in the same row of Overview have different ITEMs, we will returned a concatenated string
        {"All", each  Table.Distinct(Table.TransformColumns(_, {"ITEM", (c)=>Text.Combine(_[ITEM], ", ")}))  , 
        type table [NAME.1=nullable text, NAME.2=nullable text, NUMBER=nullable number, RANDOM=nullable text, Index=number, ITEM=text]}}),


    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Index"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"NAME.1", "NAME.2", "NUMBER", "RANDOM", "Index", "ITEM"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded All",{"Index"})
in
    #"Removed Columns3"