Lookup Range Next Value

Help Studio
, , ,
1

I have an issue where I am reading the range of an excel sheet that contains a string in the same column but on several rows. I am trying to get the cell value using lookup range so I can get the data needed from the columns next to said keyword/string that I am using lookup range on.
However the only thing I manage to get is the same output (the first value it finds) over and over again and I am not sure how to get to the next value.
I will attach a dummy excel to show better what it is I am trying to achieve!

In the dummy file, I am searching for the string “Apple” with lookup range and use the cells position to get the rest of the columns values (B-E). Then go on to the next position of “Apple” and do the same thing and add the values together.

DummyFile.xlsx (8.1 KB)

2

Hy @Robert_Wennberg,

The vLookup function only return the first record found

If you want to get all excel rows that match your criteira you must use populate the Excel File into a Data Table (using the read range activitiy) and after that use a Filter Data Table Activity to get all rows you want.

Is that what you want?

3

@William_Blech_Sister

Hey William and sorry for the late reply!

What you describe is what I want, yes. However, I only wish to grab the values in the B-E columns. Is that something that can be achieved using a Filter Data Table Activity?

4

Hy @Robert_Wennberg,

Yes you can choose what columns you want to diplay, In the Output Columns you can write (in quotes), te Columns you want

image

If you like my answer please mark my answer as solution!

image

Thank you

5

@William_Blech_Sister

I tried with that solution earlier, but I think I encounter the problem that I do not have any data at the top of the column, just 2-3 rows of whitespace so I can’t determine what columns to get data from.
How would I bypass that problem?

EDIT: For example, the data starts at B4, and everything above that is blank cells

6

Hy @Robert_Wennbergn

In that case you can do the following.

  • create a new data table
  • populate the excel file in a another data table (no headers)
  • loop through the excel data table and add records to the new data table. Use column index as there are no headers. Example row(0).to string
  • apply the filter using the apply filter method.

Does it work for you?!

7

@William_Blech_Sister

I will try out your solution and get back to you if I have further questions or if it works!
Thank you for your help this far

1 Like
8

@William_Blech_Sister

I am trying to do as you described but I am too stupid to understand.
I will attach the automation and a dummy file to better explain what I am trying to accomplish if you have time to look it over to find out what is wrong and not working?
Sorry for the inconvenience!

DummyFile.xlsx (10.2 KB) TestAutomation.xaml (8.5 KB)

9

Hy @Robert_Wennberg,

Dont be to hard on yourself, you are learning and we all make mistakes. You are trying to get it right!

Please check my workflow, let me know if you understood.:slight_smile:

Thank you!

LookUpValueTest.zip (31.0 KB)

1 Like
10

@William_Blech_Sister

Sorry for the late response and thank you for the encouraging words!

I understand the workflow better now, thank you!
I managed to create a different solution because of the whitespace above the column title.
One problem still remains though where I am trying and would like to add up all the values of “Apple” into one total value. Is there any easy way to merge those numbers together somehow with the Assign activity or alike?
Thank you.

11

Hey @Robert_Wennberg

Is there any other way to use instead of this ? I am using for each row activity so I don’t want to open and Shut Excel each time.