CODEWITHSUNDEEP
excel-formula
user3777822
user3777822

Reputation: 1

Excel - formula for two workbooks

I have a sheet 1 in workbook 2 that has numbers in column A and locations for those numbers in column B. I would like to type a number in column A in workbook 1 sheet 1 and it find that number on workbook 2 sheet 1 column A then it automatically put that location in the corresponding column B cell on workbook 1. Basically I want to type a number in column A and it tell me that location in column B from the master list (workbook 2). Is this possible? I know that both workbooks would have to be open.

Upvotes: 0

Views: 36

Answers (1)

iambasil
iambasil

Reputation: 105

You can use a VLOOKUP formula in Wb 1 sheet 1 cell B1 (assuming a number is in A1) like this:

=VLOOKUP(A1,'[workbook 2.xlsx]Sheet1'!$A$1:$B$50,2,FALSE)

If the range is not a fixed number of rows, you can swap the $A$1:$B$50 with A:B.

This assumes that each number only exists once in Column A of Workbook 2, Sheet 1.

Create the cell reference with both workbooks open, but you won't actually need workbook 2 open for the reference to work after that (the full file path will be referenced).

Hope that helps

Upvotes: 1

Related Questions