New Contributor

# How to match a list of values to values from a predetermined list

Hi there,

For my thesis I need to do some data cleaning and I'm looking for a way to not have to do this by hand (tens of columns and hundreds of rows). The data I have needs to match a prespecified set of numbers, namely:

 256 128 64 32 16 8 4 2 1 0,5 0,25 0,13 0,06 0,03 0,02 0,01

The problem is a lot of the data in my database is values such as 0.022, 0.60, 0.10, etc.

I'm looking for a way to convert these numbers to the nearest one from the list above. Rounding functions will not work since it's not a generic interval. I have tried vlookup and combinations of 'index' and 'match' but they all render #N/B for reasons unknown to me.

Does anyone have an idea on how to tackle this? I would be much obliged!

7 Replies

# Re: How to match a list of values to values from a predetermined list

@jasper1995 Here's a formula that will return the closes match in the reference list which is larger than the value looking up (column B below).

``=INDEX(\$A\$3:\$A\$18,MATCH(IF(B15>256,256,B15),\$A\$3:\$A\$18,-1),1)``

Hope this helps.

Dexter

# Re: How to match a list of values to values from a predetermined list

Hi Dexter your formula does not bring the desired result "nearest" but always the next higher value. My formula does it differently. Let's see which solution is the right one for @jasper1995 .

``=XLOOKUP(TRUE,ABS(\$A\$3:\$A\$18-B3)=MIN(ABS(\$A\$3:\$A\$18-B3)),\$A\$3:\$A\$18)``

# Re: How to match a list of values to values from a predetermined list

@dscheikey Agreed.  Good point.

# Re: How to match a list of values to values from a predetermined list

Thank you for the advice! Unfortunately as the other commenter said this still renders the next value up from, and not the nearest one.

EDIT: it works, but when inserting a value of say 0,061 it wil render 0,13 instead of 0,06.

# Re: How to match a list of values to values from a predetermined list

Thank you! Unfortunately the version of excel my employer has provided me with seems not to have the XLOOKUP formula, but I will look into this!

# Re: How to match a list of values to values from a predetermined list

I managed to figure out a different way of doing it

 Matching number closest to X Reference Halfway point Cutoff Data input Matched value 256 192 191,999999 0,013 0,016 128 96 95,999999 0,003 0,004 64 48 47,999999 0,05 0,063 32 24 23,999999 0,025 0,031 16 12 11,999999 0,025 0,031 8 6 5,999999 9 8 4 3 2,999999 5 4 2 1,5 1,499999 0,013 0,016 1 0,75 0,749999 0,013 0,016 0,500 0,375 0,374999 0,249 0,25 0,250 0,19 0,189999 0,251 0,25 0,130 0,0965 0,096499 0,0000001 0 0,063 0,047 0,046999 0,064 0,063 0,031 0,0235 0,023499 200 256 0,016 0,012 0,011999 130 128 0,008 0,006 0,005999 0,00222 0,002 0,004 0,003 0,002999 0 0,002 0,0015 0,001499 0 0,001 0,0005 0,000499 0 0 0 0 0

Columns are A through E.

A is the reference column.

B is the halfway point between two values in A.

C is the cutoff point between two values in A minus 0,00001.

D is the data column e.g. input.

E is the output value e.g. value in D matched to reference in A.

Managed to do it with this formula using the 'Cutoffs' as conditions for the 'IF' formula to use. I am curous if anyone can find flaws in this or whether I am overlooking anything. D3 is the cell it concerns as the input to be matched here.

``=IF(D3>\$C\$3;\$A\$3;IF(D3>\$C\$4;\$A\$4;IF(D3>\$C\$5;\$A\$5;IF(D3>\$C\$6;\$A\$6;IF(D3>\$C\$7;\$A\$7;IF(D3>\$C\$8;\$A\$8;IF(D3>\$C\$9;\$A\$9;IF(D3>\$C\$10;\$A\$10;IF(D3>\$C\$11;\$A\$11;IF(D3>\$C\$12;\$A\$12;IF(D3>\$C\$13;\$A\$13;IF(D3>\$C\$14;\$A\$14;IF(D3>\$C\$15;\$A\$15;IF(D3>\$C\$16;\$A\$16;IF(D3>\$C\$17;\$A\$17;IF(D3>\$C\$18;\$A\$18;IF(D3>\$C\$19;\$A\$19;IF(D3>\$C\$20;\$A\$20;IF(D3>\$C\$21;\$A\$21;0)))))))))))))))))))``

# Re: How to match a list of values to values from a predetermined list

Hello Jasper, yes, your formula also works. But you could also have rewritten my formula to INDEX() and MATCH(). XLOOKUP() is no longer so new. It is available from EXCEL 2016.

``=INDEX(\$A\$3:\$A\$22,MATCH(TRUE,ABS(\$A\$3:\$A\$22-D3)=MIN(ABS(\$A\$3:\$A\$22-D3)),0))``