Finding a value from one sheet on another sheet and transferring cell info onto both
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.
Answers
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!