CODEWITHSUNDEEP
arraysexcelexcel-formulalookup
Madrock
Madrock

Reputation: 11

Excel - Find a value within a table to return nth column value

I need to be able to return in excel a lookup value within an array and to return the last column associated with this lookup value's row.

visual example

In this simplified example I'm looking up "B-C-D" which can be found in array A2:C5, column 2, row 3 and I need it to return the value in Column D associated with this value.

Thank you very much

Upvotes: 0

Views: 373

Answers (3)

jlear
jlear

Reputation: 180

You can use =MAX((A8=A2:C5)*ROW(A2:C5)) to find the absolute row number of an element that matches your lookup value. With that, you can use index in column D to find the answer: =INDEX(D:D,MAX((A8=A2:C5)*ROW(A2:C5)))

Upvotes: 1

basic
basic

Reputation: 11968

If lookup value is unique you can use SUMPRODUCT:

=SUMPRODUCT((A8=A2:C5)*D2:D5)

if not then use AGGREGATE to get first value:

=AGGREGATE(15,6,(D2:D5/(A9=A2:C5)),1)

enter image description here

Upvotes: 2

Chris H.
Chris H.

Reputation: 325

Paste the below code in the ThisWorkbook module of the VB editor. With this any change at all to the lookup cell will trigger the macro to find the value in column d and put that in b7.

Option Explicit

Public Sub Workbook_SheetChange(ByVal ws As Object, ByVal target As Range)


If (ws.Name) = Worksheets(1).Name Then
    If Not Intersect(target, Range("a7")) Is Nothing Then
        Dim rng As Range
        Dim i As Variant
        Set rng = Range("a1:d5")

        For Each i In rng
            If target.Value = i Then
                Cells(7, "b").Value = Cells(i.Row, "d").Value
            End If
        Next i
    End If
End If
End Sub

Upvotes: 0

Related Questions