Extract values from columns or rows into one single cell with a formula in an excel report

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.

formula excel


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 ?





The formula:


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:


formula excel formula excel

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:


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
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:


Interesting Management