CODEWITHSUNDEEP
vbaexcelvlookup
R Jay
R Jay

Reputation: 23

Find Value based on looking up 2 Rows and 1 Column using Excel VBA or formulas

I am looking to understand how to use Excel VBA or Formulas to find a value with 2 specific rows and 1 specific column. I have attached an example screenshot of the table below for reference.

I would want to search 2014 in Row 1, Sally in Row 2, and the Country of Australia in Column A. The value returned would be: 2454.38

Example Table

Upvotes: 2

Views: 548

Answers (2)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Here is the VBA Excel version.

    Sub MatchingYearNameCountry()
    Dim YearName, Country, Data
    Dim Year1 As Integer, Name1 As String, Country1 As String

    YearName = Range("B1:R2")
    Country = Application.Transpose(Range("A3:A12"))
    Data = Range("B3:R12")

    Year1 = 2014
    Name1 = "Ari5"
    Country1 = "China4"

    For i = 1 To 10
        For j = 1 To 17
            If YearName(1, j) = Year1 And YearName(2, j) = Name1 And Country(i) = Country1 Then
                MsgBox "Data for " & Year1 & ", " & Name1 & ", and " _
                        & Country1 & " is " & Data(i, j)
                Exit Sub
            End If
        Next j
    Next i
        MsgBox "Data not found."
    End Sub

I'm assuming B1 = 2011, B2 = Joe, and A3 = Canada.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

You would use this array formula:

=INDEX($A$1:$M$6,MATCH(P3,$A$1:$A$6,0),MATCH(P1&P2,$A$1:$M$1&$A$2:$M$2,0))

Being an array it needs to be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done correctly Excel will put {} around the formula.

enter image description here

Upvotes: 1

Related Questions