I have two sheets in Excel:

• Sheet A contains medical equipment and the sensors they are using.
• Sheet B contains medical departments and the equipment they use.

I want to insert all the sensors of the medical equipment from Sheet A into Sheet B. How can I do this using a formula in Excel?

The final table should look like this.

I tried using this formula:

=INDEX(SheetA!B:B, MATCH(A2, SheetA!A:A, 0)) 

The problem here is, it only matches the first sensor value from Sheet A. So in this case, it only gets the "Force" sensor information.

Then I tried this formula:

=INDEX(SheetA!C:C, MATCH(B2, SheetA!A:A, 0)):INDEX(SheetA!C:C, MATCH(B2, SheetA!A:A, 1))

But I get an error for this formula. Is there any mistake that I don't see?

CodePudding user response：

I think the easiest way to accomplish this would be by using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac)

To use Power Query

• Make your two tables Tables.

  • I named them Table_A and Table_B, but you could use other names if you wish

• Select some cell in one of the Data Tables

• Data => Get&Transform => from Table/Range

• When the PQ Editor opens: Home => Advanced Editor

• Make note of the Table Name in Line 2

• Paste the M Code below in place of what you see

• Change the Table name in line 2 back to what was generated originally.

• Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//Read in both tables
//Change table names to reflect your actual table names
    Source = Excel.CurrentWorkbook(){[Name="Table_B"]}[Content],
    Table_B = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Equipment", type text}}),
    Source2 = Excel.CurrentWorkbook(){[Name="Table_A"]}[Content],
    Table_A = Table.TransformColumnTypes(Source2,List.Transform(Table.ColumnNames(Source2), each {_, type text})),

//join them
//  then expand the nested table
    Final = Table.NestedJoin(Table_B,"Equipment", Table_A,"Equipment","Final",JoinKind.LeftOuter),
    #"Expanded Final" = Table.ExpandTableColumn(Final, "Final", {"Unit", "Model", "Manufacturer"})
in
    #"Expanded Final"

Table_A

Table_B

Final Result