vlookup or join by slightly different amounts?

Question

I have two datasets and need to merge one column from one dataset to the other dataset. The issue I have is the amounts sometimes can be slightly different. Is there any way to join or look up by slightly different amounts? If not, does fuzzy match allow you to match two records from different datasets and merge columns from one to another?

DateProductAmount07/31/2024Apples50

ProductAmountRegion Apples51.25Southwest

I want to bring the region from the second dataset into the first dataset. If I create a unique key (Product +Amount), is there anyway to achieve this with slightly different values?

Thanks, all.

peterr_h · Answer

Hi @CDIns ,

I think the issue that you will have here is to do with tolerance levels.

First step is easy - join the two datasets on the 'Product' field - this will give you the following:

DateProductAmountRight_ProductRight_AmountRegion2024-07-31Apples50Apples51.25SouthWest

You could then use a formula tool to create a max and min value for 'Amount', and if 'Right_Amount' fits within these max and min then it's a match, and if not then it's not. But this will be down to a level of tolerance - you could make this 1% of the Amount (0.5, in this case, which would not match). But this would then be down to you deciding how much tolerance is acceptable. In this case, 3% tolerance would be acceptable. The formula for a 5% tolerance would look like this:

[New field name: Match. New field type: Boolean]

IF ([Amount]-(0.05*[Amount])) <= [Right_Amount]
AND
[Right_Amount] <= ([Amount]+(0.05*[Amount]))
THEN 1
ELSE 0
ENDIF

This gives the following:

DateProductAmountRight_ProductRight_AmountRegionMatch2024-07-31Apples50Apples51.25SouthWestTrue

You can adjust this as you see fit.

Hope this helps somewhat?

binu_acs · Answer

@CDIns If you are looking for a slight variance you can go for this approach

join_14082024.yxmd