Look up a value's first occurrence in a table (with a twist)

[franc1s]

I have attached an excel file with what I want to achieve. In excel it is pretty straightforward (see attached), and I suppose in Alteryx it will be too, if only I knew how to do it :-)

If possible, I would like your help with resolving this with a normal workflow first (although it feels like a batch macro would be a fit here).

The columns in yellow represent my table, it has a sequence number, a caption name, and a number identifying its place in the caption hierarchy. For each of the rows in the yellow table I want to be able to determine the caption of level just above it.

Eg, TOTAL CASH, level 4, i want to look for the name of the level just above it (3). It has to be the first immediate occurrence of 3, as there are several entries in the yellow table with 3 that wont correspond correctly. 

The way I am doing this in excel is to have the range of the vlookup move with the rows in the table (so that it doesnt find the first value in the table, but, the next occurrence).

The Join and find and replace tools dont seem to be the tools here I have also tried the summation tool to get the first occurrence, but, I havent been successful.

Thanks for any guidance you can provide.

[gabrielvilella · Accepted solution]

Hey @franc1s, I solved this using an iterative macro. Maybe someone else has an idea with no macros.

[franc1s]

Thanks @gabrielvilella! I am hoping that someone can still suggest an alternative to macros. I havent build any so far, and I dont want to make things more complicated for me and others for the moment.

Its a good opportunity to study your suggestion. Can you maybe describe what exactly it does; what each iteration accomplishes?

[franc1s · Accepted solution]

@gabrielvilella Gabriel, with a little bit more experience in macros, I now understand better what you are doing. But, can you help me with the following? Could the statement [sq] = [Engine.IterationNumber]+1 in the filter tool be replaced by something like loop through the record set?

[gabrielvilella]

I don't think you will be able to do this without a macro. The only way to loop through records is by using an iterative macro.