vlookup or join by slightly different amounts?

[CDIns]

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? 

Date	Product	Amount
07/31/2024	Apples	50

Product	Amount	Region
Apples	51.25	Southwest

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. 

[binuacs]

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

[peterr_h]

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:

Date	Product	Amount	Right_Product	Right_Amount	Region
2024-07-31	Apples	50	Apples	51.25	SouthWest

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:

Date	Product	Amount	Right_Product	Right_Amount	Region	Match
2024-07-31	Apples	50	Apples	51.25	SouthWest	True

You can adjust this as you see fit.

Hope this helps somewhat?