Vlookup multiple column values

Hi,
I have 2 files and I am doing a vlookup. I was succesfull in getting 1 column of data, however I need to get multiple columns of data. I am attaching the sample files and xml file. The sample file is right now limited but in my business use case the columns keeps varying.

Marks.xlsx (10.3 KB) MasterData.xlsx (8.5 KB) Main.xaml (3.2 KB)

Hi @manjesh_kumar,

One possible solution could be to copy & apply the solution created by you on a different column, and afterwards check the result.

It could be a series of vlookups, or you can use some kind of flowchart for efficiency, for instance:
VLOOKUP_Check_Column1 —> Result 1
If Result 1 is Nothing, then
VLOOKUP_Check_Column2 —> Result 2
If Result 2 is Nothing, then … and so on

Hope this helps.
Best regards,
Marius

Hello @Marius_Puscasu,

Thank you for the response

I have mentioned that in my use case there are more than 25 columns and it can vary, is there a way where I can loop until I find the last column data.

Hi @manjesh_kumar,

It does not matter how may columns do you have.

You can use a For Each loop to iterate thru all of your columns, then you can modify your lookup activity in order to receive as parameter a column at the time.

When your lookup returns a value, then you have found the match and, afterwards you can go to next row …

Best regards,
Marius

Hello @Marius_Puscasu,

How do i get the column address like ‘D’, ‘E’ for me to put this in a loop dynamically. Currently the cell value where i write is static which I want to change dynamically.

"C"+(DT1.Rows.IndexOf(row)+2).ToString

Regards
Manjesh

Hi @manjesh_kumar,

You can use an array to get all the columns from your data table:

arrColumnnames = (From dc In yourDT.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray()

Then you can use a For Each loop to iterate thru your columns arrColumnnames

In order to correctly identify a position you could use this assignment

yourDT.Rows(yourDT.Rows.IndexOf(row))(item.ToString) = your value

where item.ToString is referring actually to your column name

Therefore in your specific case,
"C" +(DT1.Rows.IndexOf(row)+2).ToString
can be replaced with
DT1.Rows(DT1.Rows.IndexOf(row)+2)(item.ToString)

Hope this helps
Best regards,
Marius

Dear @Marius_Puscasu,

Thank you for the response. Please don’t get me wrong.

.

Regards,

Manjesh

Hi @manjesh_kumar,

But you have the column address. In the solution mentioned above, the identified column names are included within the column address you are looking for

For instance,
"B" +(DT1.Rows.IndexOf(row)+2).ToString has the same value as
DT1.Rows(DT1.Rows.IndexOf(row)+2)(item.ToString) (when item.ToString is “Name” <-- item from the For Each loop). It’s just a different way to reference a cell, and it is dynamic…

Best regards,
Marius

Dear @Marius_Puscasu,

In this current example there is a column name but in the real use case there is no column name and I have to rely on the column id .

After I do a vlookup and write in the column “C” then I need do a vlookup for the same column “ID” from master.xlsx column and then write start writing the data in “D” Column of master.xlsx.

Regards,

Manjesh

Hi @manjesh_kumar,

I’m afraid that that I don’t know what are you looking for.
You have already created a lookup activity for one column, which is running well.
Now just surround it with a For Each activity & some result checks and you are applying the lookup to a whole range…

Best regards,
Marius

Dear @Marius_Puscasu,

Thank you for being patient and trying to help me, appreciate that. :slight_smile:

Regards,
Manjesh