pulling adjacent values from another workbook?

Question

I have two separate workbooks: monthly shipping report and shipping log. In the monthly shipping report I have a column with the tracking numbers of shipments that were sent to customers. In the shipping log there are worksheets for various customers with the tracking numbers and shipping cost. I want to update the monthly report with shipping costs. In the monthly shipping log, how do I create a formula to lookup a tracking number in a specific worksheet in the monthly shipping report and return the value that is found two columns over and add this the monthly shipping log?

Answer 1

Hi, Holly Hoover

Thank you for using Microsoft products and posting them to the community.

You can use the VLOOKUP function, as an example:

Assume:

• The monthly transportation report workbook is “Report.xlsx”.
• The transportation log workbook is “Log.xlsx”.
• The waybill number in the monthly transportation report is in column “A”.
• The waybill number in the transportation log is in column “A” of “Sheet1” and the transportation cost is in column “C”.

Using VLOOKUP

Open both workbooks: make sure “Report.xlsx” and “Log.xlsx” are open.

Entering formulas in the Monthly Transportation Report: In “Report.xlsx”, let's say that you need to enter transportation costs in column “B”. You can enter the following formula in cell “B2”:

= VLOOKUP(A2, [Log.xlsx]Sheet1!$A$1:$C$100, 3, FALSE) 

• A2: This is the waybill number to look up.
• [Log.xlsx]Sheet1!$A$1:$C$100: This is the range of data in the shipping log.
• 3: Indicates that the value in column 3 of the range is returned, i.e. the transportation cost.
• FALSE: indicates an exact match.

Drag Formula: Drag the formula from “B2” to the desired row to populate the transportation cost of other waybill numbers.

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist