Forum Discussion
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!
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)))))))))))))))))))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))
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
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)dscheikey Agreed. Good point.
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.
