vlookup in different workbooks

[Himansu]

Hello everyone,

Is it possible to use "vlookup" when comparing ranges in different
workbooks? Let me know what you think. Any examples would be great.

 

[IanKR]

Is it possible to use "vlookup" when comparing ranges in different

workbooks? Let me know what you think. Any examples would be great.

Yes - just fully-reference the table_array range thus:

=VLOOKUP($A2,'[<workbookname.xls>]<worksheetname>'!$A$4:$G$13,B$12,FALSE)

Is this what you mean?

 

[Bernard Liengme]

The formula below does a VLOOKUP of A1's value in a table on Sheet2 of a
workbook called Bernard. It works just fine provided Bernard.xls is open

=VLOOKUP(A1,[Bernard.xls]Sheet2!$A$1:$B$10,2,FALSE)
best wishes

 

[Himansu]

Hi Ian,

For some reason this doesn't work:
--

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)

--

** The cell doesn't even get treated as a formula inside it....

Is it possible to use "vlookup" when comparing ranges in different
workbooks? Let me know what you think. Any examples would be great.

Yes - just fully-reference the table_array range thus:

=VLOOKUP($A2,'[<workbookname.xls>]<worksheetname>'!$A$4:$G$13,B$12,FALSE)

Is this what you mean?

 

[IanKR]

For some reason this doesn't work:

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)


** The cell doesn't even get treated as a formula inside it....

Do you mean that the ar cell ref doesn't resolve the A4 when you hit Enter?
Does the false resolve to FALSE? What does it return in the cell? #N/A! ?
#VALUE! ?

 

[Bernard Liengme]

Use Edit Clear All on the cell in question
Type this much: =VLOOKUP(A4,
The activate the other workbook and use the mouse to select the range
Sheet1!$A:$B
Finish by typing: ,FALSE)
Does it work now?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hi Ian,

For some reason this doesn't work:
--

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)

--

** The cell doesn't even get treated as a formula inside it....

Is it possible to use "vlookup" when comparing ranges in different
workbooks? Let me know what you think. Any examples would be great.

Yes - just fully-reference the table_array range thus:

=VLOOKUP($A2,'[<workbookname.xls>]<worksheetname>'!$A$4:$G$13,B$12,FALSE)

Is this what you mean?

 

[IanKR]

For some reason this doesn't work:

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)


** The cell doesn't even get treated as a formula inside it....

Do you mean that the ar cell ref doesn't resolve the A4 when you hit
Enter? Does the false resolve to FALSE? What does it return in the cell?
#N/A! ? #VALUE! ?

I meant "... the a4 cell ref ..."

I take that the value in cell A4 is somewhere in col A in
[VFW_Cell_Dept_Matrix.xls]Sheet1! ?

 

[Himansu]

I tried this:

=VLOOKUP(A4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A$1:$B$300,2,FALSE)


---
but I get a N/A when it clearly avaialble...

For some reason this doesn't work:

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)


** The cell doesn't even get treated as a formula inside it....

Do you mean that the ar cell ref doesn't resolve the A4 when you hit Enter?
Does the false resolve to FALSE? What does it return in the cell? #N/A! ?
#VALUE! ?

 

[IanKR]

I tried this:

=VLOOKUP(A4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A$1:$B$300,2,FALSE)

but I get a N/A when it clearly avaialble...

Check for rogue leading and/or trailing spaces in both the col A values of
the other wkb and also in your A4 value of the first wkb.

 

[Himansu]

Thanks!!! that was the problem.....I changed the attributes of the columns
and now it works fine....

I tried this:

=VLOOKUP(A4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A$1:$B$300,2,FALSE)

but I get a N/A when it clearly avaialble...

Check for rogue leading and/or trailing spaces in both the col A values of
the other wkb and also in your A4 value of the first wkb.

 

[Himansu]

Thanks for all your help!!!

Use Edit Clear All on the cell in question
Type this much: =VLOOKUP(A4,
The activate the other workbook and use the mouse to select the range
Sheet1!$A:$B
Finish by typing: ,FALSE)
Does it work now?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Hi Ian,

For some reason this doesn't work:
--

=VLOOKUP(a4,[VFW_Cell_Dept_Matrix.xls]Sheet1!$A:$B,2,false)

--

** The cell doesn't even get treated as a formula inside it....

Is it possible to use "vlookup" when comparing ranges in different
workbooks? Let me know what you think. Any examples would be great.

Yes - just fully-reference the table_array range thus:

Is this what you mean?