How to compare 2 sheets and harvest data from one sheet onto the second sheet

kstaver

I am comparing 2 versions of a product BOM(one is flat, and the other is organized by sub assemblies and lists critical components). Each BOM is stored in its own Smartsheet Grid. One BOM has vendor and vendor part number information. I want to harvest the vendor and vendor part information from one of the BOMs and insert it into the other BOM. What is is the best formula to do that?

Part numbers are consistent between the two BOMS. We have two versions because they are serving two different purposes. I want to use a formula that looks for the part number in one sheet and then collects the vendor information and puts it into the other sheet.

Is this possible? Help?

Darren Mullen

@kstaver Look up "Index Match" here in the community for lots of examples.

kstaver

I am making some progress, but I am still having some issues.

I have this formula: =INDEX({ES1 Flat BOM Range 1}, MATCH({ES1 Flat BOM Range 2},@row, {ES1 Flat BOM Range 3}, 0))

I am getting the #UNPARSEABLE error

I attached 2 screen shots: 1 of each sheet. I want to bing in the Primary Vendor information from the ES1 Flat BOM into the ES1 Parts, subs and vendors.

Screenshot 2023-01-16 at 12.01.15 PM.png

Screenshot 2023-01-16 at 11.59.30 AM.png

Dale Murphy

@kstaver I think you have an unwanted comma:

I don't know if that is the solution, but it is part of the problem.

Actually, that set of brackets should reference a local range and thus be square not swirly. At least that is how I use the function.

Cheers,

dm