Recreate Excel lookup in Alteryx

[sann0638]

I am trying to recreate columns I and K in Alteryx in the attached, basically looking up for a particular value in all rows up to the one before the current row.  Multi row formulas could work if it were only a few, but there will be 1000s of rows.

Interesting challenge, I think!

[Wesley Mendonca]

Hi!

I didn't quite understand the main goal of this workflow. Are you trying to find a specific value (a constant) within a range of rows before the current one?

For example, do you need to check for the presence of "150"? If it's found one or more times, the current row would take the value "150"; otherwise, it would be "N/A."

Is that what you mean? Or are you looking to reference another field to retrieve key information?

[sann0638]

Thanks for looking.  I want to find "what is the latest instance of home ranking after where the home coach ID matches the home coach ID in the current line" - basically it's looking for "what is the current ranking of the coach at the start of the game, which was the ranking of that coach after the last game they played".  The Excel may not be exactly right, thinking about it, it should have a qualifier in the XLOOKUP.

[sann0638]

Simplified and working workbook.  Just need a formula that does what column C does, but in Alteryx (due to replicability, number of rows etc).

[Wesley Mendonca]

Hi! @sann0638 

I was little busy here, but I think I created some solution for your case. 

I used ID_record to manipulate the order by Coach and after that a sorted by coach and ID_record, in order to get the expected result!

So after that I resorted by record_ID. 

So now you be able to use Multirow formula tool.