if

Copper Contributor

I need this value to fit these ranges  

 $      39.03 =
Min.GoodBetterBest
 $   32.11 $     35.68 $      36.70 $   39.52
3 Replies

@ashika565 

 

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.52

@ashika565  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")