Vlookup Equivalent - TRUE / Close Match

[sam_mulholland]

I am trying to use the 'Join' function as the Vlookup equivalent in Alteryx, but Vlookup has a function where it will bring back a close match. I have a table of data below, which I am trying to join with another table, however it is only 'joining' when the exact match is found.

For example:

365
322
201
42
110
79
7
128
305
224
189

I then have another table with the following:

0

12

32

228

239

I would like to join them together to have the following:

365	239
322	239
201	32
42	32
110	32
79	32
12	12
128	32
305	239
224	32
189	32

In this example, the only thing that is currently joining is the '12' as it's an exact match.

[OTrieger]

@sam_mulholland 

What is the logic of your joining as I can see 32 and 201 joined together. Why 32 and 201 need to join together, just as an example?

Give us the logic we might be able to help solving it.

Why 228 is not matched to any value?

[sam_mulholland]

The logic is if the number in table 1 is between two in table 2, it should return that number. 32 and 201 join because the next higher number in table 2 is 228. There is nothing in table 1 between 228 and 239, so 228 would not return.

[OTrieger]

@sam_mulholland 
I see so you have a range of numbers and if the number is in that range then it is a match.

Good than this is the logic that you need to use to get it done.

You can label each number on the 2 table with a value, as category, and create the ranged and join based on the ranges and you will get these match.
There are other ways, however the above is simple to do and will be dynamic based on the values in table 2, if will change in the future.

[OTrieger]

you can use formula tool to check bigger than and smaller than, you can actually generate the whole range of values in that range, the are many ways how to do it. I just wrote a simple way that not requires any knowledge of the more advance tools

[sam_mulholland]

Do you have an example of that formula tool? Thanks for your help.

[sam_mulholland]

Do you have an example of that formula? Thanks for your help.

[lwolfie]

Not sure if you figured this out, but here's an example workflow of something to try.

[jrlindem · Accepted solution]

What I believe you're trying to do is match using a "ceiling."

One note though, your original dataset that you provided has a 7 whereas I think you intended to include a 12, so I overrode that value.

Then, if you create an intentional cartesian using the append, you can use logic to isolate only the values that fall "under" the target and use a filter to dump the values above and a Summarize to grab the MAX value:

Workflow attached as well.  Hope that helps,  -Jay