Is there any formula that I can use to make a double-layer search?

I want to be able to gather whatever information on the third column that meets two criteria.

1. It has to be one location (in this case, Indonesia)
2. It has to be a particular industry (FMCG)

Example here:

CodePudding user response：

If industry size is a numeric field, you got 2 options to get this. If not, then you can do it only with MATCH and INDEX

With MATCH/INDEX: =INDEX($C$2:$C$11;MATCH(F17&G17;A2:A11&B2:B11;0))

With SUMIFS: =SUMIFS($C$2:$C$11;$A$2:$A$11;F18;$B$2:$B$11;G18)

Depending on your Excel version, the MATCH formula may need to be introduced as an array formula so you must introduce it pressing CTRL SHIFT ENTER

CodePudding user response：

If you want a VBA solution, please use the next function:

Function extractIndSize(strCountry As String, strInd As String, rng As Range) As Variant
    Dim i As Long, arr
    arr = rng.value
    For i = 1 To UBound(arr)
        If arr(i, 1) = strCountry And arr(i, 2) = strInd Then
            extractIndSize = arr(i, 3): Exit Function
        End If
    Next i
End Function

It can be tested using the next way:

Sub testExtractIndustrySize()
  Dim sh As Worksheet, lastR As Long, rng As Range
  
  Set sh = ActiveSheet
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).Row
  Set rng = sh.Range("A2:C" & lastR)
  
  MsgBox extractIndSize("Indonesia", "FMCG", rng)
End Sub

The function can also be called (as UDF) from a cell, using the next formula:

 =extractIndSize(E2,F2,A2:C11)

Where in "E2" the country name must be introduced, in "F2" the industry name and the third parameter is the range to be processed (selected with the cursor),,,