Vlookup Only When Certain Criteria Are Matched
I want to link a vendors sheet to our total orders sheet and run a vlookup to fill in the fields automatically when the vendor updates them but I can't figure out how to do this without locking the cells for other vendors on our total orders sheet.
For instance ; when carrier says "Transit X", vlookup field Y. Is there a way to do this without locking the column for manual updates either through a formula or an automation.
Answers
-
Hi,
Yes, it is possible to link the vendor sheet to your total orders sheet and run a VLOOKUP to fill in the fields automatically without locking the cells for other vendors.
Here are the steps you can follow:
- Create a column in your Total Orders sheet for the vendor name, where you will enter the name of the vendor for each order.
- In the Vendor sheet, create a column for the vendor name and enter the name of the vendor for each record in the sheet.
- In the Vendor sheet, create a column for the field you want to lookup in the Total Orders sheet, such as Transit X.
- In the Total Orders sheet, create a column where you want to display the result of the VLOOKUP formula.
- In the cell where you want to display the result of the VLOOKUP formula, enter the following formula:
=VLOOKUP([Vendor Name]@row, VendorSheetRange, [Transit X Column Number], false)
Replace "Vendor Name" with the column name in your Total Orders sheet where you have entered the vendor name for each order. Replace "VendorSheetRange" with the range of the Vendor sheet that includes the vendor name and Transit X columns. Replace "Transit X Column Number" with the number of the column in the Vendor sheet that contains the Transit X field. For example, if the Transit X field is in column 3, use 3 as the column number. The "false" parameter at the end of the formula indicates that you want an exact match for the vendor name.
- Copy the formula down to all the cells in the column where you want to display the VLOOKUP results.
This formula will automatically look up the Transit X value for the vendor in the Vendor sheet and display it in the Total Orders sheet, without locking the cells for other vendors. If the vendor updates the Transit X value in the Vendor sheet, the VLOOKUP result in the Total Orders sheet will be updated automatically.
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
Thank you! I was able to accomplish this by creating a report and allowing our vendors to enter data into the report as well :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!