Returning a Cell Value

[bhbjk1]

I have 2 workbooks, Book 1 and Book 2.

Book 1 has a number in Cell A1. I want to find Book 1 Cell A1 value in a
Cell in Book 2 from a Column (lets say B) of numbers. Once I find that value
I want to return the value of a different cell in that row.

Can this be done?

 

[Max]

One way is to use INDEX/MATCH which allows you to match on any col, then
return any other col to the left/right of the match col

In Book1, in Sheet1,

Assume in A1 down contains the numbers to be matched

you could place this in B1:
=INDEX('[Book2.xls]Sheet1'!$C:$C,MATCH(A1,'[Book2.xls]Sheet1'!$B:$B,0))
Copy B1 down as far as required

The MATCH part of it:
MATCH(A1,'[Book2.xls]Sheet1'!$B:$B,0)
will match A1 with what's within col B in Book2.xls's Sheet1

When the match is found, this INDEX part:
INDEX('[Book2.xls]Sheet1'!$C:$C
then retrieves the corresponding value from col C in Book2.xls's Sheet1

If there's no match found, you'd get an ugly #N/A. If you want to return
clean looking blanks: "" instead for unmatched cases, use this in B1:
=IF(ISNA(MATCH(A1,'[Book2.xls]Sheet1'!$B:$B,0)),"",INDEX('[Book2.xls]Sheet1'!$C:$C,MATCH(A1,'[Book2.xls]Sheet1'!$B:$B,0)))

Hope the above clarifies the index/match workings. Adapt to suit.

 

[Max]

Clarification. The preceding formulas assumes that
you have both Book1.xls & Book2.xls open simultaneously

---

 

[bhbjk1]

Worked Great Thanks

 

[Max]

welcome, Jeff.