VLOOKUP to fetch data from CSV file

It depends on the format of the csv file.

Does the source data size change each day?

The csv file would need to  replace the previous one in the same folder and have the same name, i.e. delete the old one or rename first.

VLOOKUP will normally work across workbooks without the source workbook being open. I'm not sure about csv files, can you provide an example of what you are working with.

If the CSV file maintains the same name and location, you can set up a Data Connection to it so that it imports data into the workbook. The VLOOKUP can then reference the data connection result.

Also just tested using a VLOOKUP from an excel workbook to a CSV file and it works. There is an issue with updating links so it might not refresh correctly when the CSV is updated unless you open the CSV at the same time.

@Roy, Yes the file size keeps changing everyday. And it always comes to the same location with the same name by overwriting the old one.


@Rob, Yes, the CSV file comes to the same location and has the same name. Can you give me the formula for the VLOOKUP that you tried? Because, when i apply the formula, all i get is #NA. I don't know where I am going wrong. Maybe I am looking into the incorrect array values.

#NA will mean it is not finding the search term. Can you provide dummy workbook & csv. It is also important that the data is the same size in the new csv

Formula I used:

=VLOOKUP(A2,'H:\Desktop\Stuff\[List.csv]List'!$A:$F,2,FALSE)

I had the List.csv file open at the time when I cretaed the formula and then closed it, hence the file path within the formula.

With the csv file open the formula would be:

=VLOOKUP(A2,List.csv!$A:$F,2,FALSE)

Please refer the attachments, In the file B.xlsx, the data in the last row and F column is missing. How do I fetch the data from the CSV file.
A.csv
B.xlsx

Unfortunately your CSV isn't comma separated, it is separated by semi-colon. Maybe this is a regional setting for you.

The formula I am trying to give is VLOOKUP(E5,A.csv!A:G,FALSE). But it gives #NA error.

Does it matter whether it is comma separated or semicolon separated? The VLOOKUP does not work for comma separated?

Open the csv in Excel.
Use TexttToColumns to separate the entries to columns using ; as the delimiter

Note csv file is from your file opened in Excel and the TextToColumns completed
B.xlsx
A.csv

@Roy, I want to fetch it without converting the CSV flle.

VLOOKUP(E5,A.csv!A:G,FALSE)

Your VLOOKUP syntax is wrong in a couple of ways.
Syntax should be:

=VLOOKUP(LookupValue,LookupRange,Offset,LookupType

1) LookupValue - the value to be found in lookup range, the vlookup will look at only the first column of lookup range. Your formula is looking for the Value in E5 in range A:G so will look in column A. Column A of your lookup range is job name whereas column E is End Dates.

2) LookupRange - your CSV file columns A to G

3) Offset - the number of the column from which to retrieve the value for LookupValue, you have missed out this part.

4) LookupType - FALSE or 0 will attempt to find an exact match of the lookupvalue. TRUE or 1 will find the nearest match that is less than the lookupvalue; this assumes the lookuprange is in ascending order on the first column.

Sorry, That was a typing mistake. I use this formula. =VLOOKUP(E5,A.csv!A:G,6,FALSE). It still gives me the same error. My guess is the offset value 6 is incorrect as there are no columns in CSV file

With that syntax, the formula will look for the value from E5 in column A and return the result from column F.

I think you should have:
 =VLOOKUP(E5,A.csv!E:F,2,FALSE)

Rob's  import suggestion works for me.

@Rob, even =VLOOKUP(E5,A.csv!E:F,2,FALSE) dint work for me. May I know the steps for the data connection. I will give that a try.

In your Excel file, you can create a data connection to the CSV file; I have just done it and it recognised the semi-colon as the separator.

On data tab, Select "from Text/CSV", browse to your CSV file and click Import. This shows a preview of the CSV file separated into columns; there is a dropdown option at the top of the preview to change the delimiter but mine automatically recognised the semi-colon. Click Load. The CSV will then be loaded into the Excel file as a table but it will have also created a link to the source file so as and when the source file changes you can just refresh the data connection. With the query sheet selected and the cursor in the table there will be an extra tab on the ribbon bar with Query tools, there is a button on that ribbon for Refresh.

You can then do the VLOOKUP on the imported data.

But this will be the same as the converting the original CSV file and doing the VLOOKUP right?

I assume your local settings for Dates are "mm/dd/yy" whereas mine is "dd/mm/yy" so when I import the date columns it does not recognise them as dates but puts them as text string that looks like a date.

I would assume that if your local settings are "mm/dd/yy" and the imported date is in the same format, it would recognise it as a date.

Not quite the same as converting the original, it will create a linked copy of the CSV file within your workbook.

Does the settings apply when the file is in network drive also? The CSV file will be generated in a network drive.

Shouldn't be an issue, when you create the data connection it will create the link to the network location.