An useful formula to return a value from another column if matching multiple criterias, sometimes, I may use vlookup but it may happen that the result is not what I want so I am using this one. It also allows to match criteria with few characters, not need to put the full word, very useful if I need to match a word in a sentence.

 

When I use the formula ?

To extract the value when I need to match multiple criterias.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formula ?

=INDEX()

=MATCH()

=ISNUMBER()

=SEARCH()

The formula:

=INDEX(B2:B10,MATCH(TRUE,ISNUMBER(SEARCH("United-States",A2:A10)),0))

To get the result, I will need to press on the keyboard “CTRL + SHIFT + ENTER” if not, I will get an error.

The formula will work the same if I put “ted” instead of “United-States”, for instance:

=INDEX(B2:B10,MATCH(TRUE,ISNUMBER(SEARCH("ted",A2:A10)),0))

Now let´s say that I need to match another criteria, not only the country but also the priority, in this case, I will just need to add another “search” like this:

=INDEX(B2:B10,MATCH(TRUE,ISNUMBER(SEARCH("ted",A2:A10)*SEARCH("P1",C2:C10)),0))

To extract unique values for a row, I will have to create first a code, press the F11 key on your keyboard, it should open the macro then on the left side, you should see “module1”, put this code:

Function ccnoduprow(RowRange As Range) As String
Dim X As Long
Dim CellVal As String
Dim ReturnVal As String
Dim Result As String
' change the comma "," by what you want
Const Delimiter = ", "
For X = 1 To RowRange.Count
  ReturnVal = RowRange(X).Value
  If Len(RowRange(X).Value) Then If InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) = 0 Then Result = Result & Delimiter & ReturnVal
Next
ccnoduprow = Mid(Result, Len(Delimiter) + 1)
End Function          
              

Once done, just close it and use the “ccnoduprow” function that we just created so the formula will be like that:

=ccnoduprow(A2:H2)