Excel Lookup Range - Entire Worksheet

Hi, I need to find a cell with a specific value in the entire worksheet. How do I do this with the Excel Lookup Range activity? Esp. since specifying the range beforehand will limit the search area or cause it to search an unnecessarily wide area

What do you intend to do with that value? you could export the entire table as a string and search for the value that way if you only intend to know, True or False, if it exists.

Otherwise you use dynamic ranges within a do while loop and once the value is encountered the cell reference can be returned and the search halted.

@ronanpeter With the address index I can find the start of a table, which is always found next to that cell

This should help though there may be a better way to do it without the column array.

It uses the Lookup Range activity across the whole excel file looking for a certain value and gets the cell reference. Then, using the below code and an array of excel column references {A, B, C,…}, it will create a Target Cell Reference for you to identify where your table starts.

TargetCell = (arrColumns((Convert.ToInt32(Array.IndexOf(arrColumns, Left(OutputRef, 1)).ToString))+1)) + (Right(OutputRef, 1).ToString)

FindValueCellRef.zip (7.9 KB)

this file seems corrupted, can yo uplease reshare it!!

many thanks

Sure. It may be corrupted due to Studio version or packages requiring updates.

FindValueCellRef.zip (8.2 KB)

It takes forever to load, any suggestions

what takes an age to load? it runs in about 10seconds for me

This an example which may be helpful: https://www.youtube.com/watch?v=R5-UMlh2EWY

why I’m getting error at “CellIndexValue” , any package should i import?