How to find matches that are equal to or closest to and less than the current value?

[flying008]

Dear all, hello !

How to find matches that are equal to or closest to and less than the current value?

Please look at the data. I want to search for the current row of [Old_Value] in the field column [New_Value]. If I can find an equal value, I will return the value of the corresponding row [New_Name] where the equal value is located. If I cannot find an equal value, I will match the value of the corresponding row [New_Came] where the value closest to and less than [Old_Value] is located, just like using the Approximate match option of the Excel function xlookup.
For example, I am searching for 30 in [New_Value], but [New_Value] does not have a row equal to 30. The item closest to 30 and less than is 29, so we return the value 'P' of [New_Name] corresponding to the row where 29 is located.

Input			Output
Old_Name	Old_Value		Old_Name	Old_Value	Get
a	30		a	30	P
b	25		b	25	M
c	10		c	10	N
d	2		d	2	C
Match
New_Name	New_Value
U	32
P	29
M	24
N	8
C	2

[OTrieger]

@flying008 
You can do it with a nested batch macros, first macro will take Input and then second macro will take Match so what will happen the first macro will take row a and then the second macro will looking for the matching and both macros will continue working until that all the entries will be matched.

[binuacs]

@flying008 one way of doing this with the append tool

[binuacs · Accepted solution]

@flying008 attaching the macro and non-macro versions of the workflow

[flying008]

Hi, @binuacs  & @OTrieger 

Dear all, 

Thank you for your response. Both the Append tool and Macro can achieve the expected results, but my data has a large number of rows, so I am currently continuing to search for other non macro solutions that do not generate Cartesian products. Thank you again for your help!

[OTrieger]

@flying008 

I see, in this way you can try to do the following. Get the data through Join tool, then you will have the exact match. With the remaining for the values you can use multiple raw formula tool to get the differences between the rows, with generate row tool add that number of rows to each value, and then add the raw number -1 and you will get all the as running values, use once again the Join tool and you will get the rest of the missing matching. This is a the general idea, you will need to add more logics to ensure that it working well.

[Gaurav_Dhama_ · Accepted solution]

Can you test if this works for your large dataset?

[flying008]

Hi, @Gaurav_Dhama_ 

Just so so ! 😁