How to use vlookup for two different excel file?

[suraj_gaikwad]

Hello Everyone,

I have two two different excel files and those two excel files i have to download regular basis and use vlookup ( File name like invoice report 30Apr2024__3636) other file name ( Po 30Apr2024_3737)

In first image where i have to use vlookup

Second image select First All columns

When i do manually i m getting this output which i want
In below image

When i run the bot this output i m getting

Thanks.

[lrtetala (Lakshman Reddy)]

Hi @suraj_gaikwad

Can you please share your excel files with dummy data.

Regards,

[suraj_gaikwad]

Cant share the file @lrtetala

[Ajith_Pandi_T]

Instead Of Using VlookUP , u can try with the LINQ , it’s will provide the expected output in short time .

Create A variable - updatedDataTable (Data Table - Var Type)

updatedDataTable =

From row1 In DataTable1.AsEnumerable()
Group Join row2 In DataTable2.AsEnumerable()
On row1(“PO_Name”).ToString() Equals row2(“PO_Name”).ToString() Into Group
From row2 In Group.DefaultIfEmpty()
Select NewDataRow = {
If(row2 Is Nothing, “NA”, row2(“Column1”)),
If(row2 Is Nothing, “NA”, row2(“Column2”)),
If(row2 Is Nothing, “NA”, row2(“PO”) = row1(“PO_Name”))
}
).CopyToDataTable()

and finally use the write range to write the output data

[suraj_gaikwad]

Once the vlookup done then automatically output should come for all columns @Ajith_Pandi_T

[Ajith_Pandi_T]

Could you please provide a brief overview of the columns and files? With that information, I can share my ideas accordingly.

[suraj_gaikwad]

Hello,

First i select PO column in from excel1 and use vlookup at first column and select column 1 from PO NO
Refer below image

Then go to to second Excel1 file and select column PONO

Then back to first Excel1 and enter, this is the output

( Basically we are using vlookup for copy same value )

@Ajith_Pandi_T @lrtetala

[lrtetala (Lakshman Reddy)]

@suraj_gaikwad

Can you show the code how you are doing

Regards,

[suraj_gaikwad]

This is working but file name should be change everyday with date.

And i m getting output as NA for all columns
@lrtetala

[lrtetala (Lakshman Reddy)]

@suraj_gaikwad

You can pass the file name dynamically in a variable like below

FileName="Excel2.xlsx"
"=VLOOKUP(B2,[" + FileName + "]Sheet1!$B:$B,1,0)"

Excel1:(Input)

Excel2:(Input)

Excel1:(Output)

Cheers!!

[suraj_gaikwad]

I m getting only NA output

@lrtetala

[suraj_gaikwad]

@lrtetala

Have any idea to remove na

[mukesh.singh]

Here’s how you can use VLOOKUP with two different Excel files, although VLOOKUP itself cannot directly reference data from another file:

1. Combine both Excel files into a single file:

• Open both files and copy the data you want to reference.
• Paste the copied data into the appropriate location in the file where you want to use VLOOKUP.
• This is the simplest method, but it might not be ideal if you want to keep the original files separate.

2. Use POWER QUERY to import data (Excel 2016 or later required):

• Go to the Data tab and click “Get External Data” > “From File”.
• Select the other Excel file and click “Import”.
• The Power Query Editor will open.
• You can shape the data as needed and name the table in the destination file before closing the editor.
• This method allows you to reference data from another file while keeping the original files separate.

3. Use VBA macro to import data:

• VBA offers more flexibility than VLOOKUP for importing data from another file.
• However, using VBA requires programming knowledge.

Option 1 is likely the most straightforward solution, but consider options 2 or 3 depending on your specific scenario.

[lrtetala (Lakshman Reddy)]

@suraj_gaikwad

If you want to remove #N/A try below

Dim rowsToRemove = dtInput.AsEnumerable().Where(Function(row) row.Field(Of String)("ID").Equals("#N/A")).ToList()
For Each rowToRemove In rowsToRemove
    dtInput.Rows.Remove(rowToRemove)
Next

Cheers!!