New Contributor

I need this value to fit these ranges  

 $      39.03 =
 $   32.11 $     35.68 $      36.70 $   39.52
3 Replies



Please describe the situation (cell references; any existing formulas) and the outcome you are looking for.  For example, "I need a formula that does [...what?...]".


Do you want a formula in B1 that searches a range of values in A3:D3 (32.11, 35.68, 36.70, 39.52) and returns the value that is closest to the value in A1 (39.03)?


If so, then in older versions of Excel (like mine), array-enter (press ctrl+shift+Enter instead of just Enter) the following into B1:




In newer versions of Excel, you might be able to simply press Enter.


And you might be able to use the LET function to calculate ABS(A1-$A$3:$D$3) only once.  For example, normally-enter the following into B1:


=LET(x, ABS(A1-$A$3:$D$3), INDEX($A$3:$D$3, MATCH(MIN(x), x, 0)))


hi Joe,

i want $39.03 to equal "Min, good, better or best criteria." For example, $39.03 falls under better criteria.

Min Good Better Best
$ 32.11 $ 35.68 $ 36.70 $ 39.52

@ashika565  wrote:  ``For example, $39.03 falls under better criteria.``


Much better explanation!  But you might be omitting some other details.




=LOOKUP(A1, {32.11, 35.68, 36.70, 39.52}, {"Min", "Good", "Better", "Best"})


where the value in A1 might be 39.03.




1. Enter 32.11, 35.68, 36.70 and 39.52 into X1:X4

2. Enter Min, Good, Better and Best into Y1:Y4


Then the formula is:


=VLOOKUP(A1, $X$1:$Y$4, 2)


But what if A1 is less than 32.11?


If that might an issue, write:


=IFERROR(VLOOKUP(A1, $X$1:$Y$4, 2), "Error")