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

[jasper1995]

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!

[#2]

@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

[#3]

@DexterG_III 

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)

[#4]

@dscheikey Agreed.  Good point.  

[#5]

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.

[#6]

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!

[#7]

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)))))))))))))))))))

[#8]

@jasper1995 

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))