Apply V-look up

datatable
excel

#1

Hi,

I have to apply v-look up referring 2 excel sheets and copy 2-3 columns of one excel sheet into the new sheet based on v-look up results. Example: Apply v-look up of column 1 of excel sheet 1 with column 3 of excel sheet 2. Based on the match, add column 8, 9 and 10 of excel sheet 2 into a new excel sheet 3. Please provide a sample flow to achieve it.
As primary steps, I tried doing it with activity “Merge data table” but 2nd data table rows are appended after completion of 1st data table rows which was unexpected behavior. I also tried “for each row” loop inside another “for each row” loop which turned horribly wrong.


#2

Hello,

please take a look on these two links.

Happy automation :sunglasses:: if you found the answer of your question pleasae mark it as solved.


#3

I have already tried two links before approaching the forum and both direct to “How to simulate Excel VLookup in a Datatable. Filter Datatable.” It pertains to assigned value in a variable and I tried to modify it according to my need but no luck.
Could you please provide a sample flow to achieve my solution?


#4

Could anyone please help here?


#5

Hello,

I bump this topic, i’m also looking to the same information


#6

I also need help with this. I am entering the vlookup formula in the appropriate cell. I need to create a table which points at the appropriate data range in excel. I can do this using hot key (cntrl-t) but need the name to be fixed. To fix the name I am using hot keys (alt-jt then a then type the new table name). The problem I face is that UiPath cannot find the table name object.

The selector is:

<wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - salesData' /> <wnd cls='MsoCommandBar' title='Ribbon' /> <wnd cls='MsoWorkPane' title='Ribbon' /> <wnd aaname='Ribbon' cls='NetUIHWND' /> <wnd aaname='Table Name:' cls='RichEdit20W' />

The error I get is:

Message: Cannot find the UI element corresponding to this selector: <wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - salesData' /><wnd cls='MsoCommandBar' title='Ribbon' /><wnd cls='MsoWorkPane' title='Ribbon' /><wnd aaname='Ribbon' cls='NetUIHWND' /><wnd aaname='Table Name:' cls='RichEdit20W' /> Source: Type into 'RichEdit20W' Exception Type: SelectorNotFoundException

I am using Excel 2007. Can anyone help?


#7

Sorry, the selector is:

<wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - salesData' /> <wnd cls='MsoCommandBar' title='Ribbon' /> <wnd cls='MsoWorkPane' title='Ribbon' /> <wnd aaname='Ribbon' cls='NetUIHWND' /> <wnd aaname='Table Name:' cls='RichEdit20W' />


#8

Just a sidenote - when posting code, please use preformatted text - enclose your text in grave accent marks (or double them for multiline).

See Markdown rules.


#9

Or this button

<wnd app='excel.exe' cls='XLMAIN' title='Microsoft Excel - salesData' />
<wnd cls='MsoCommandBar' title='Ribbon' />
<wnd cls='MsoWorkPane' title='Ribbon' />
<wnd aaname='Ribbon' cls='NetUIHWND' />
<wnd aaname='Table Name:' cls='RichEdit20W' />

#10

Applying a Vlookup is really simple:
The Syntax of Vlookup is : =VLOOKUP( lookup_value, table_array, column_index, range_lookup )

where:

  1. ‘lookup_value’ specifies the value to be searched inside the ‘table_array’.
  2. ‘table_array’ is the range with two or more columns.
  3. ‘column_index’ is the relative index of the column whose value needs to be returned by the VLOOKUP function.
  4. ‘range_lookup’ is a Boolean value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

I would suggest you to have a look at this article for further details on the topic.