Forum Discussion

ilker_d's avatar
ilker_d
Copper Contributor
May 24, 2019

Value Search On 3 Criteria

Good morning buddies,

 

I'm trying to find a formula that kind search in multiple criteria. (see attachment for the Excel sheet)

It needs to search in the Groups in Sheet ''1'', From there it needs to search on row 2, and from there it needs to matching criteria on the first sheet B1. So when I change the Value in B1 all data should change. Could someone please help me with this?

 

Thanks in advance

 

Best regards,

 

Ilker 

admin
developer
excel
Excel Online
Formulas and Functions

5 Replies

  • Berndvbatanker's avatar
    Berndvbatanker
    Iron Contributor
    May 24, 2019

    ilker_d 

    Hi,

    i create a userdefinded function. Certainly there is also a formula for this, but i like VBA

     

    Function GetData(strGroup As String, strNr As String) As Double
    Dim rngFindColumn As Range
    Dim rngFindRow As Range

    Set rngFindColumn = Tabelle2.Rows(2).Find(what:=strGroup, lookat:=xlWhole)
    If Not rngFindColumn Is Nothing Then
    Set rngFindRow = Tabelle2.Range("A:A").Find(what:=strNr, lookat:=xlWhole)
    If Not rngFindRow Is Nothing Then
    GetData = Tabelle2.Cells(rngFindRow.Row, rngFindColumn.Column).Value
    End If
    End If

    End Function

     

    call from cell B3: =GetData($B$1;$A3)

     

    Regards

    Bernd

    https://vba-tanker.com/

    • ilker_d's avatar
      ilker_d
      Copper Contributor
      May 24, 2019

      Berndvbatanker 

      Thank you for your reply but I don't know what a VBA means.

      Isn't there just a formula that I can add into cell B3?

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 24, 2019
        Perhaps you need to manually enter your desired results, then attach your file again so I can understand the logic of the formula you need. Formula, not VBA, should be the default solution.