nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

flying008

Dear all, hello !

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

Fuzzy Match

Accepted answers

binu_acs

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

NEw_Value_16082025.yxzp

Gaurav_Dhama_

Can you test if this works for your large dataset?

Closest Match.yxmd

All comments

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.

binu_acs

@flying008 one way of doing this with the append tool

NEw_Value_16082025.yxmd

binu_acs

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

NEw_Value_16082025.yxzp

flying008

Hi, @binu_acs & @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_

Can you test if this works for your large dataset?

Closest Match.yxmd

flying008

Hi, @Gaurav_Dhama_

Just so so ! 😁

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15908

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703