vlookup multiple workbooks

[Churley]

I am attempting to use VLOOKUP to link 2 workbooks together.
This formula works fine:

=VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),

However, when I try to copy this formula into another workbook where the
workbook name has a space in it, i.e., test 1, I get this error message: Name
invalid.
Any Suggestions??

[Dave Peterson]

If you build your formula by typing:

=vlookup(a4,
then use Window (on the worksheet menu bar) to select the other workbook
then go to sheet1 of that other workbook and point at the range (a4:a1000),
you'll see that excel will add some apostrophes.

=VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE)

I've found that excel does a much better job with syntax like this than I do--so
I let it do the heavy lifting.

ps. You may want to look at =match() to see if that does what you need.

=match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)

kind of like:
=if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope")

I think most people use =vlookup() to return a value from an adjacent cell.


Churley wrote:
>
> I am attempting to use VLOOKUP to link 2 workbooks together.
> This formula works fine:
>
> =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
>
> However, when I try to copy this formula into another workbook where the
> workbook name has a space in it, i.e., test 1, I get this error message: Name
> invalid.
> Any Suggestions??

--

Dave Peterson

[SimonCC]

You'll need single quotes ( ' ) around the filename and sheetname when
there's a space involved in the name. So rather than just:
=VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE)
you'll need:
=VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE)

-Simon


"Churley" wrote:

> I am attempting to use VLOOKUP to link 2 workbooks together.
> This formula works fine:
>
> =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
>
> However, when I try to copy this formula into another workbook where the
> workbook name has a space in it, i.e., test 1, I get this error message: Name
> invalid.
> Any Suggestions??
>

[Churley]

"Dave Peterson" wrote:

> If you build your formula by typing:
>
> =vlookup(a4,
> then use Window (on the worksheet menu bar) to select the other workbook
> then go to sheet1 of that other workbook and point at the range (a4:a1000),
> you'll see that excel will add some apostrophes.
>
> =VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE)
>
> I've found that excel does a much better job with syntax like this than I do--so
> I let it do the heavy lifting.
>
> ps. You may want to look at =match() to see if that does what you need.
>
> =match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)
>
> kind of like:
> =if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope")
>
> I think most people use =vlookup() to return a value from an adjacent cell.
>
>
> Churley wrote:
> >
> > I am attempting to use VLOOKUP to link 2 workbooks together.
> > This formula works fine:
> >
> > =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
> >
> > However, when I try to copy this formula into another workbook where the
> > workbook name has a space in it, i.e., test 1, I get this error message: Name
> > invalid.
> > Any Suggestions??
>
> --
>
> Dave Peterson
>

[Churley]

"SimonCC" wrote:

> You'll need single quotes ( ' ) around the filename and sheetname when
> there's a space involved in the name. So rather than just:
> =VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE)
> you'll need:
> =VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE)
>
> -Simon
>
>
> "Churley" wrote:
>
> > I am attempting to use VLOOKUP to link 2 workbooks together.
> > This formula works fine:
> >
> > =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
> >
> > However, when I try to copy this formula into another workbook where the
> > workbook name has a space in it, i.e., test 1, I get this error message: Name
> > invalid.
> > Any Suggestions??
> >

[Churley]

Thank you for your response.This was very helpful!


"SimonCC" wrote:

> You'll need single quotes ( ' ) around the filename and sheetname when
> there's a space involved in the name. So rather than just:
> =VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE)
> you'll need:
> =VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE)
>
> -Simon
>
>
> "Churley" wrote:
>
> > I am attempting to use VLOOKUP to link 2 workbooks together.
> > This formula works fine:
> >
> > =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
> >
> > However, when I try to copy this formula into another workbook where the
> > workbook name has a space in it, i.e., test 1, I get this error message: Name
> > invalid.
> > Any Suggestions??
> >

[Churley]

Thank you very much. This was very helpful!!

"Dave Peterson" wrote:

> If you build your formula by typing:
>
> =vlookup(a4,
> then use Window (on the worksheet menu bar) to select the other workbook
> then go to sheet1 of that other workbook and point at the range (a4:a1000),
> you'll see that excel will add some apostrophes.
>
> =VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE)
>
> I've found that excel does a much better job with syntax like this than I do--so
> I let it do the heavy lifting.
>
> ps. You may want to look at =match() to see if that does what you need.
>
> =match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)
>
> kind of like:
> =if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope")
>
> I think most people use =vlookup() to return a value from an adjacent cell.
>
>
> Churley wrote:
> >
> > I am attempting to use VLOOKUP to link 2 workbooks together.
> > This formula works fine:
> >
> > =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE),
> >
> > However, when I try to copy this formula into another workbook where the
> > workbook name has a space in it, i.e., test 1, I get this error message: Name
> > invalid.
> > Any Suggestions??
>
> --
>
> Dave Peterson
>