The goal is to have a formula that when you enter the item number on column A it will return the category name on column B.
I will have multiple item numbers, each one will get 1 category name.

Item # Category Search Item # Category
95200-020-01 Car 2555-044-85 44568-052-03 Bike
95200-040-02 Car 44568-052-03 2555-044-85 Toy
45887-011-08 Toy
44568-052-03 Bike
2555-044-85 Toy
99557-845-25 Bike
33894-516-87 Car


Code:
// Join
let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Join = Table.NestedJoin(Table1,{"Search"},Table2,{"Item #"},"Table2",JoinKind.LeftOuter),
    Exp = Table.ExpandTableColumn(Join, "Table2", {"Item #", "Category"}, {"Item #", "Category"}),
    RC = Table.RemoveColumns(Exp,{"Search"})
in
    RC