How to perform a vlookup in 2 datatable

FINAL
I have two data tables 1 and 2 based on column A i have to perfom a vlookup and fill column C in data table1 with column B values of data table 2

Use assign activity
dt1.rows(0)(3)=dt.Select(β€œa=’”+dt1.Rows(0)(0).ToString+"’")(0)(1).ToString

Can you share a sample xaml

is any solution for Vlookup on 2 datatables?

i am searching for the same but not found any solution.

if any one knows please provide me

my data table contains minmum of 1000 rows and 55 columns

use for each for datatable 2
dt (contains text to find)
for each row in datatable (contains column text to find and column result text)
row.item(3)=dt.Select(β€œa=’”+Row.item(0).ToString+"’")(0)(1).ToString
next row

Hi Atrimal,

My excel contains atleast 500 rows and 500 rows.
if i apply above logic it will be 500* 500
and also i have to vlookup on e excel files
hence my robot is crashing.

upgrade you hardware config. or wait till the process completes as this much rows will take time.
You can have log message for each row in loop to check how many rows completed or which row is in progress

during execution of process itself Robot is crashing and i am getting popup robot crashed.
Hence i am looking for alternative solution.

i found solution in Linq like below

var q = from dataRows1 in dt.AsEnumerable()
join dataRows2 in dt1.AsEnumerable()
on dataRows1[β€œID”] equals dataRows2[β€œID”] into lj
from r in lj.DefaultIfEmpty()
select dtResult.LoadDataRow(new object
{
dataRows1[β€œID”],
dataRows1[β€œName”],
dataRows1[β€œAge”],
r == null ? 0 : r[β€œSalary”]
}, false);

when i changed above code as per my requirement i am getting error can any one help on this.

HI @sunilkanth,

Check this out using the Invoke code

:slight_smile:

Includes:-
1.Deletion of rows
2.Insertion columns
3.Filteration
4.vlookup

Excel_manuplation_R1_with_R2.xaml (12.3 KB)

Includes:-
1.vlookup
Vloook_sample.zip (10.9 KB)

Regards
Sanjay Shankhla

2 Likes