CODEWITHSUNDEEP
excelexcel-formula
Sray
Sray

Reputation: 25

How to find a cell with closest value using Excel worksheet function

I took a snapshot of this excel sheet as follows.

          D             E           F
1         
2
3         =?            67           a
4                       45           b 
5                       12           c
6                        3           d

I want to assign to D3 any of the alphabets from a,b,c or d based on the following rule.
First, I have to get the closest number to 20 from the items in E3 through E6. In this case E5(12).
Then, search the next cell to the right of the result obtained in the first step to get the alphabet value c.
My question is, what functions must I put in D3 to perform this thing?

Upvotes: 1

Views: 9671

Answers (2)

paxdiablo
paxdiablo

Reputation: 882646

I'm going to show you a slightly different approach, how to create a user-defined function (UDF) to do this. It may not be strictly required in this case but it's very useful to know the process.

At a certain point, regular Excel formulae can become unmanageable and/or unreadable as they get more complex, so it's sometimes advisable to learn how to code in VBA.

Have a look at the following function:

Option Explicit

Public Function getVal(c As String, r As Integer)
    getVal = Range(c & CStr(r)).Value
End Function

Public Function paxLookup( _
    lkupCol As String, _
    dataCol As String, _
    sRow As Integer, _
    eRow As Integer, _
    data As Integer _
) As String
    Dim diff As Integer
    Dim newdiff As Integer

    ' Invalid range means no data. '

    paxLookup = ""
    If eRow < sRow Then Exit Function

    ' First row is initial best. '

    diff = Abs(data - getVal(lkupCol, sRow))
    paxLookup = Range(dataCol & CStr(sRow)).Value
    sRow = sRow + 1

    ' Check all rows, getting better ones. '

    While sRow <= eRow
        newdiff = Abs(data - getVal(lkupCol, sRow))
        If newdiff < diff Then
            diff = newdiff
            paxLookup = getVal(dataCol, sRow)
        End If
        sRow = sRow + 1
    Wend
End Function

Entering that code into the Excel Visual Basic editor will allow you to use the formula:

=paxLookup("e","f",3,6,20)

in your cells, to get the information you're after. The arguments are:

  • the look-up column, holding the values you want to check.
  • the data column, holding the value you want returned.
  • the start row, and
  • the end row for searching.
  • the data you want to get closest to.

The use of VBA for user defined functions allows arbitrarily complex code, while still being readable.

Upvotes: 0

L42
L42

Reputation: 19737

Try this array formula:

=INDEX(F$3:F$6,MATCH(MIN(ABS(20-E$3:E$6)),ABS(20-E$3:E$6),0))

This is entered in D3 using Ctrl+Shift+Enter.

Upvotes: 3

Related Questions