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...
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! Unfortunately the version of excel my employer has provided me with seems not to have the XLOOKUP formula, but I will look into this!
