Recreate Excel lookup in Alteryx

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!

Game Data.xlsx

Workflow

Accepted answers

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.

new_13.12.png

Autosave_20241212155047_28580_5.yxmd

All comments

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).

Game Data.xlsx

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.

new_13.12.png

Autosave_20241212155047_28580_5.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703