Forum Discussion
I need this value to fit these ranges
| $ 39.03 = |
| Min. | Good | Better | Best |
| $ 32.11 | $ 35.68 | $ 36.70 | $ 39.52 |
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:
=INDEX($A$3:$D$3,MATCH(MIN(ABS(A1-$A$3:$D$3)),ABS(A1-$A$3:$D$3),0))
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.52ashika565 wrote: ``For example, $39.03 falls under better criteria.``
Much better explanation! But you might be omitting some other details.
Ostensibly:
=LOOKUP(A1, {32.11, 35.68, 36.70, 39.52}, {"Min", "Good", "Better", "Best"})
where the value in A1 might be 39.03.
Better:
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")
