Vlookup using linq query

I have two data table, Dt1 and DT2 wherein with column(10) of Dt1 vlookup has to be performed with Dt2 Col No3 and the result to be printed to another Datatable.

Kindly, let me know if there’s a solution for this.

Regards,
Sudharsan S

Hii @Sudharsan_S

Hope this helps,

I tried this,but its not actually working…
Dt1(Col 10) has 1000 rows and when Vlookup is done with Dt2(Col No 3, which has 20000 Col)
The result gotten should be dT3 with 1000col…
But, I am not getting that result.

Regards,

@Sudharsan_S
Can you share the sample input file ?

New Microsoft Office Excel Worksheet.xlsx (8.9 KB)
Comparing col 10 with col 3 as highlighted…

HI,

How about the following expression?

dt2.AsEnumerable.Where(Function(r2) dt1.AsEnumerable.Any(Function(r1) r1(9).ToString=r2(3).ToString)).CopyToDataTable

And also check the following sample?

Sample20230316-4L.zip (11.0 KB)

If it’s not what you expect, can you share your expected result?

Regards,

Hey @Sudharsan_S ,
As per your Sample file

Input1 :-

Input2 :-

Both input table contains only one column each .
My question is if there is only one column why do you need Vlookup you can just join the tables .

(
From x In dt_Input1
Join y In dt_Input2 On x(“10”).tostring Equals y(“Policy”).tostring
Select dt_Output.LoadDataRow(New Object(){x(“10”).tostring},False)
).CopyToDataTable

Give a try to this one for your sample excel file

1 Like

there are values in all the columns…
my bad,I sent u the wrong excel.
but,u get my point right.
I’ll send another one if needed

@Sudharsan_S ,
I have modified your sample input file and i got the result .
Please check the below workflow and updated excel file
VlookUpLinq.zip (2.2 KB)

New Microsoft Office Excel Worksheet.xlsx (10.4 KB)

Use below Linq query
(
From row1 In dt_Input1.AsEnumerable()
Let a=dt_Input2.AsEnumerable.Where(Function(row2) row2(“Policy”).ToString.Trim.Equals(row1(“10”).ToString.Trim)).ToList
Let b=If(a.Count()>0,a(0)(“digits”),“”)
Let c={row1(“10”).ToString,b}
Select dt_Output.Rows.Add(c)
).CopyToDataTable

Hope this helps, :slightly_smiling_face:

1 Like