Finding a value from one sheet on another sheet and transferring cell info onto both

Galadriel975

I need help figuring out a formula that will cross reference between two sheets.

I have one sheet that stores our open orders and another that tracks our orders undergoing quality assurance. What I need is a formula that takes the order number from the open order sheet and searches for it on the quality assurance sheet, then takes the status column and notes column from the QA sheet and populates that info on the open order sheet for that order. I assume that this would have to have several functions nested together, probably VLOOKUP, but I'm not sure where to go from there. I'd appreciate any insight into making this work.

Adam Murphy

Use Index/Match to collect the values. Assuming you want the data in two fields on your open orders sheet, add those two columns, for the Status one, use a formula like =INDEX({QS Status}, MATCH([Order Number]@row, {Order Number}, 1)) and then for Notes something like =INDEX({QS Notes}, MATCH([Order Number]@row, {Order Number}, 1)) . You have to setup the {} variables, of course and replace the [Order Number] with the correct column name. Hope this helps!