Lookup range exact value

Help
, ,
1

I have a datatable with a For each row, and inside of that for each row I am using Lookup range to find the current rows cell number. The only problem is that when going through each row, there might be a similar value thus grabbing the wrong cell value.
For example, a list of names, where A3 contains the string “Leonardo DiCaprio” and the cell A10 contains only “Leonardo”. If I use the lookup range with the value “Leonardo DiCaprio” it will find the cell A3, but the same thing will happen if the lookup range value is only “Leonardo”, and not A10.
Sorry for the confusing question but I hope there is a solution to where you can use the lookup range with an exact search!

2

Hey Robert.

It you want to find the current rows cell number in a For Each an easier solution is to go to Properties-Output in the For Each Row and then create an Output Index variable (integer).

That will work, you can test it with a Write Line IndexVariable.ToString.

Kind regards, Anders

1 Like
3

Hey Anders!

Thank you for your quick response.
I will try out your solution and if possible will get back to you if it is not working and if I don’t understand how to do it!

Best regards
Robert

1 Like
4

Hi @Robert_Wennberg

You can use following linq query

rowNumber=dt.rows.indexof((from dr as datarow in dt where dr.item("ColumnName").tostring.equals("Leonardo") select dr).copytodatatable())+1

Above code will give you exact row number for Leonardo.

5

Sorry for responding late!

I tried out your solution and it will definitely help me in further projects. However, in this case, my read range datatable I am using is starting from cell T9, and the Output Index variable always starts from 0 and not the cell value that I would like (in this case, 9). Am I doing something wrong is that the intended function?

Best regards
Robert

6

Hey Robert,
I am also looking for a solution for such a scanerio using lookup range. If you have found the correct way to do it, please help!