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

New Contributor

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

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

 

DexterG_III_1-1663689116124.png

Hope this helps.  

 

Dexter

 

 

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

 

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.

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!

I managed to figure out a different way of doing it

Matching number closest to X  
ReferenceHalfway pointCutoffData inputMatched value
256192191,9999990,0130,016
1289695,9999990,0030,004
644847,9999990,050,063
322423,9999990,0250,031
161211,9999990,0250,031
865,99999998
432,99999954
21,51,4999990,0130,016
10,750,7499990,0130,016
0,5000,3750,3749990,2490,25
0,2500,190,1899990,2510,25
0,1300,09650,0964990,00000010
0,0630,0470,0469990,0640,063
0,0310,02350,023499200256
0,0160,0120,011999130128
0,0080,0060,0059990,002220,002
0,0040,0030,002999 0
0,0020,00150,001499 0
0,0010,00050,00049900
000  

 

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

 

 

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