Problem solving VLOOKUP dynamic cell / row

Help
1

Hey everybody,

I am having little difficulties for a personal workflow I want to automate.
With that said, I have an Excel with over 1000 lines and filter one of the columns for blank cells. For this step, I use a windows shortcut (Ctrl+g; see picture below) and everything works fine. However, as soon as every cell is selected and I simply have to fill in the VLOOKUP function and press [Ctrl+Enter] to fill it in everywhere, I am having issues making this function dynamic.

My actual problem is that I don’t know in which particular row I start to insert the function (because its a repetitive analysis and its different every day) and therefore don’t know how to name the first part of the function (marked bold): =IFERROR(VLOOKUP(A2;B:E;4;0);“”)

I thought about using an index for the individual row which is selected first and using a dynamic variable in the function - just to insert the row number. But it didn’t really work. Maybe someone of you can give me a helping hand here, which would be very highly appreciated. :slight_smile:

If that was too cryptic and you are unsure about what I mean, just let me know and I try explaining it in different words.

Best regards

image
image468×825 35.5 KB

Disclaimer: The message box was just as a personal checkpoint and has no specific meaning.

2

I think some magic with this formula can help ‘’=ADDRESS (ROW(),COLUMN())“.
It should return of address of current cell. And I think you can adjust it with something like that
‘’=ADDRESS (ROW(),1)”

1 Like
3

Hey Uemoe,

it was not necessarily the solution I was looking for, but it gave me the right input to find a solution.
So for anyone else looking for this in the future. I solved the problem with a ‘type into’ activity and the following formula: “=VLOOKUP(INDIRECT(ADDRESS(ROW();1;4));A:D;4;0)”

Tbh - it was more an excel question than a UiPath question, but it works now.
best regards