Vlookup Only When Certain Criteria Are Matched

TAbs

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.

J Tech

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:

1. Create a column in your Total Orders sheet for the vendor name, where you will enter the name of the vendor for each order.
2. In the Vendor sheet, create a column for the vendor name and enter the name of the vendor for each record in the sheet.
3. In the Vendor sheet, create a column for the field you want to lookup in the Total Orders sheet, such as Transit X.
4. In the Total Orders sheet, create a column where you want to display the result of the VLOOKUP formula.
5. 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.

1. 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.

TAbs

Thank you! I was able to accomplish this by creating a report and allowing our vendors to enter data into the report as well :)