Confirm an Account is with an Account Range

[julwrigh]

Hi, I have 2 data sources. 1 is a list of Account numbers. The other is a list of Account ranges. I need to do look up of each Account number in list 1 'v' list of account ranges. If the account is within a range, need to confirm the ID, if not within a range, need to confirm n/a or no:

List 1 - dummy account number example:

Account Number

026FCECT8

List 2 - dummy - Account Range From & To::

Account From        Account To         ID

026CCACR0           02979999Z          501

Because the Account Number - Is within the from & to account number range would need it to acknowledge that and bring back ID 501. Can anyone help as I can do this with formula in excel but clueless in alteryx...

[binu_acs]

@julwrigh What is the logic used to calculate this in Excel? Could you share your Excel formula? Maybe we can convert that formula into an Alteryx equivalent.

[julwrigh]

excel formula:

=FILTER(Scope[ID],(Scope[AccountFrom]<=B2)*(Scope[AccountTo]>=B2),"Add to Table")

so 'scope' in above is just the worksheet range 

[binu_acs]

@julwrigh one way of doing this with the batch macro

[julwrigh]

thank you for looking at this. I will give this a go in the morning and let u know :)