Table lookup based on 2 criteria

[amoraru]

Hi Alteryx Community,

I am having troubles trying to look up for the band of a policy based on 2 criteria. 

This is the table that I am using for lookup:

Band	Product 1	Product 2	Product 3	Product 4
0	1	1	1	1
100,000	2	1	1	1
250,000	3	1	2	1
250,001	3	1	2	2
1,000,000	3	1	3	3

In my table I have information about a policy, what is the product and face amount. I want to map the band based on product and face. 

This is somewhat the desired output: 

Series	Face	Band
Product 1	99,999	1
Product 1	250,001	3
Product 2	350,000	1
Product 3	1,000,005	3

Can I please ask for help on how can I achieve this? Thanks a lot! 

[clmc9601 · Accepted solution]

Hi @amoraru,

I'm not entirely sure I understand your question or your data. Why do the [Band] columns have totally different values? Where does [Face] match in your second dataset? If this was originally an excel lookup, can you provide how you managed this in excel?

Regarding the Alteryx solution, have you tried using a Filter tool? You can filter to display only rows where both [Face] = 'certain value' and [Product] = 'certain value'

If this helps, please consider marking it as a solution so others may find it. Happy solving!

[binay2448 · Accepted solution]

Find attached solution for the same..

[amoraru]

Thanks a lot!