LookUp Range Activity - How to get the index of "second occurrence/Second Match" of a value?

For instance, am looking for “Product” keyword in an excel. I need the index of its second occurrence. Is it possible to get it using Look up range activity? Coz Look up activity just gives the index of first occurring “Product” keyword.

May be try this way -

Assuming your products names are in a column A.
Find the last row of column A. (read range for column A, dataDatable.Rows.Count)
Do lookup range for product - you will get address of first found say “A2”
Increment the found address range, say “A3”

Again use the lookup range and mention the Range property as "A3:A" + lastRow.ToString

It will do search only in the given range.

  • Range - The range where you want to perform the search. This field supports only strings and String variables.

@KarthikByggari Changing the range and getting the Index is a good one. Will try and let you know the result. But is there any other activity to capture the index of all the “Keyword” present in an excel???

Something like looping an activity and getting the Index for every “Keyword” hit and we can make use of the respective Index.

I have to check. Will let you know if I find something related.

any update?

Unfortunately no.
An alternate way is to use a macro to get all the cells where the search value is found.

Karthik Byggari

Hey Sriram - here is the exact video which will answer this question: https://www.youtube.com/watch?v=R5-UMlh2EWY

Try doing it by this logic
Store the column to a datatable let’s suppose dtNames
Now declare a string variable of name which you want to search let’s suppose strName
and create a int32 variable which keeps the count of how many times strName has appeared let the variable name be intHasOccured
use a for each row and iterate through the dtNames, inside this row write a if condition where you will
check if the row.item(“Name”) = strName
inside this ‘if’ activity add another if activity
in the inside ‘if’ check weather the value intHasOccured = 2 if true then get the index using dtNames.Rows.indexOf(row)
else use assign activity to assign the value intHasOccured = intHasOccured + 1

Let me know if it works