New Contributor

# if

I need this value to fit these ranges

 \$      39.03 =
 Min. Good Better Best \$   32.11 \$     35.68 \$      36.70 \$   39.52
3 Replies

# Re: if

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

# Re: if

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

# Re: if

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