Mar 14 2018 05:51 AM
i need to vlookup data found in a second excel file - spreadsheete 2. i am struggling to find the syntax to add to the vlookup formula in spreadsheet 1. can you advise?
Mar 14 2018 06:02 AM
=VLOOKUP(CC,DD,1,FALSE)
where CC = Value you would like to look up in the current sheet.. eg A4 or B3
DD = the selection you need to make in the second workbook against which you wish to lookup the value. eg.. [Book1]Sheet1!$A$2:$A$7
Instead of typing the name of the workbook and sheet simply select the criteria with the mouse.
1 = this is usually the column number of the selection criteria that hosts the value you wish to display in case a match is found, therefore it can be 2, 3.. totally depends upon the selection you make.
False = lookup the exact match
@Chad Briddell wrote:
i need to vlookup data found in a second excel file - spreadsheete 2. i am struggling to find the syntax to add to the vlookup formula in spreadsheet 1. can you advise?
Mar 14 2018 06:08 AM
Rohan, thanks. however....oddly...when i have both spreadsheet file 1 and 2 open and i am in file 1 creating the vlookup and i mouse to file 2, the formula does NOT populate with the name of the second file. i tried to insert the following....but did not work.
=vlookup(C12,[2018-Std-Cost.xlsx]Sheet1!A2:D10,2,false)
Mar 14 2018 06:26 AM
Chad, close the second file (or both, when re-open), file path will be added to link automatically.
Mar 14 2018 06:54 AM
Sergei - thanks for responding. still no success.
1 - i closed both files. then opened file 1 and in the cell where i want the value returned via vlookup i have:
'=vlookup(C12,[C:\Chad\Data\2018-Std-Cost.xlsx]Sheet1!A2:E10,5,false)
then, i removed the " ' " from the front of the formula and i get an error message: "The syntax of this name isn't correct".
2 - i tried in a new cell to create the vlookup and open the second file during the process of creating the vlookup. Excel errors and states i have entered "too few arguments" and won't let me open the second file.
is there some kind of Excel/Option setting that is off?
Mar 14 2018 07:17 AM
That's perhaps you add sheet name manually. It shall be like this
Both files are opened
=VLOOKUP(D13,[Example5.xlsx]Sheet1!$A$1:$B$4,2,FALSE)
second file is closed, full link autogenerated
=VLOOKUP(D13,'G:\MyFolder\[Example5.xlsx]Sheet1'!$A$1:$B$4,2,FALSE)
Mar 14 2018 07:30 AM
If the files are in the same directory don't mention the full path
instead of =vlookup(C12,[C:\Chad\Data\2018-Std-Cost.xlsx]Sheet1!A2:E10,5,false)
try =VLOOKUP(C12,[2018StdCost.xlsx]Sheet1!A2:E10,5,FALSE)
Change the file name from 2018-Std-Cost.xlsx to 2018StdCost.xlsx
The '-' in the file name may be causing the problem.
@Chad Briddell wrote:
Sergei - thanks for responding. still no success.
1 - i closed both files. then opened file 1 and in the cell where i want the value returned via vlookup i have:
'=vlookup(C12,[C:\Chad\Data\2018-Std-Cost.xlsx]Sheet1!A2:E10,5,false)
then, i removed the " ' " from the front of the formula and i get an error message: "The syntax of this name isn't correct".
2 - i tried in a new cell to create the vlookup and open the second file during the process of creating the vlookup. Excel errors and states i have entered "too few arguments" and won't let me open the second file.
is there some kind of Excel/Option setting that is off?
Mar 14 2018 07:37 AM
Rohan, not necessary to rename the file, something like this
=VLOOKUP(D14,'G:\MyFolder\[Example-5 with space 10a-123.xlsx]Sheet1'!$A$1:$B$4,2,FALSE)
works
Mar 14 2018 07:40 AM
Sure.. but when I worked on the syntax he was using it was the '-'
@Sergei Baklan wrote:
Rohan, not necessary to rename the file, something like this
=VLOOKUP(D14,'G:\MyFolder\[Example-5 with space 10a-123.xlsx]Sheet1'!$A$1:$B$4,2,FALSE)works
which were erroring out.
Mar 14 2018 07:50 AM
thanks Rohan. i removed the "-" from the second file name. also, instead of manually typing the formula, i had the wizard create it. while using the wizard, i was not able to open a second file. so i tried again and opened both files and started the wizard. everything worked as expected. i am good to go....i hope! thank you