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