How to get column index of a value which is stored in a variable in an excel .For example - i have stored 'feb-19' inside a variable called value and then i want to get the column index of the cell having this value in an excel

Hi @Gaurav07,

As per my understanding, you are reading an excel in a datatable and checking the headers of the datatable. Whenever you find the value “Feb-19” then you want to return 783.61.

If my understanding is correct then please consider the below approach:-

  • Firstly have the inputted value in the variable (Value = “Feb-19”) (Please mind the case sensitivity)

  • Read range to read the entire excel into the datatable variable (select AddHeaders). (lets say dtResult)

  • Using the following LINQ assign operator, you can get all the column names:
    arrColumnnames = (From dc In dtResult.Columns.Cast(Of DataColumn)
    Select dc.ColumnName).ToArray()
    where arrColumnnames is a string array.

  • Now, using For each item in arrColumnnames
    if (item.tostring.contains(Value)) then fetch the value below it.

Thanks and Regards,
@hacky