How to use vlookup in excel without using macros

Hi All,

I want to use VLOOKUP formula in my excel sheet and autofill for entire row.
I want to create a new column in the Workbook_1 called “col 2” and VLOOKUP the “col 1” in the Workbook_2 file.

I don’t want to use macros as it would require change in extension. Looking forward for a solution.

Thanks in advance.

have a check on

  • lookup datatable
  • Join datatable
  • LINQ

Could you share some code snippet in case of LINQ in above case scenario.

Thanks in advance.

lets have a look to some sample data before. Can you provide it to us? Thanks

For example, I want to query below VLOOKUP statement :
=VLOOKUP(C2,‘C:\Users<file location>[exclusion.xlsx]Sheet2’!$A:$B,2,FALSE)

What could be the LINQ query for this ?


I want to apply LINQ query for VLOOKUP in column 3 ( need to add this as new column ) of file1 by matching the values from Sheet2 of file2 in table range $A:$B for 2nd column which is col B.

I have attached screenshot for the same.

Thankyou.

Hello @Smreti_Gupta,

Would be ok to use Excel Application Scope?

If it is ok for you.

1.Read range
2.Assign: i = 2 (Int32 Variable)
3. For each row in DT
3.1. Write cell activity: Cell= “C” + i.ToString / Text: “=VLOOKUP(A:A,‘C:…Sheet2’!A:B,2,0)”
3.2. Assignt: i = i+1

@Angel_Llull : Thankyou for your help but I am looking for a LINQ query as it will be efficient. Let me know if you have any solution :slight_smile:

@ppr

in general we do use the join concept:

we would recommend following:

  • clear the details like join type: expected output …
  • do a start with the join datatable activity and postprocess the join result to get the needed output

in case of this will not serve your needs then we can check for a LINQ

Hi @ppr,

Thankyou for your kind support.

But appreciate if you could share one example code in LINQ itself.

find a starter help here:
InnerJoin_1Col_Result_AllLeft-1Right.xaml (10.7 KB)

Hello @ppr - Thankyou for your great support.
However, There is a if condition that I want to add in this which is,
if the value matches, then it will add it to the new column and if it doesn’t match then it should fill blank. As VLOOKUP works in this way only.

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(0).toString Equals d2(0).toString
Let ra = d1.ItemArray.Append(d2(“Comment”)).toArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

image

Kindly assist me.

had you tried Join DataTable?

we would use LEFT JOIN for this

Thanks @ppr It worked

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.