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)
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...