If Statements Returning Multiple Values Excel

Microsoft Excel 2003 (full product)
November 2, 2010 at 09:17:22
Specs: Windows XP
Is it possible to build several if statements that return multiple values if satisified to one cell in excel. For instance, I have the following Info in Excel:

Cell A1: Coborrower Cell B1: John Doe
Cell A2: Principal Cell B2: Jane Doe
Cell A3: Coborrower Cell B3: John Smith
Cell A4: Coborrower Cell B4: Jane Smith
Cell A5: Guarantor Cell B5: Jack Doe

In Cell G6, I want to create an if statement that says if coborrower is found in cell A1, A2, A3, A4, or A5 then return the corresponding values of the B Column seperated by commas. For instance, in the scenario above I would want G6 to read: John Doe, John Smith, Jane Smith


See More: If Statements Returning Multiple Values Excel

Report •

#1
November 2, 2010 at 09:18:16
The result listed above does not have to be seperated by commas but would be preferred.

Report •

#2
November 2, 2010 at 10:37:08
You can't do it with any built-in Excel functions, but a this User Defined Function (UDF) should work:

Open the VBA editor and insert a new standard Module.
Paste this code into the pane that opens.

In the case of your example, enter either one of these formula in G6:

=MultString(A1)

or

=MultString("Coborrower")

Option Explicit
Function MultString(ByVal myCell As String)
Dim rw As Integer
Dim tmpString As String
Application.Volatile
'Loop through Column A data
'If the value in a cell matches the string we are looking
'for, add the value in Column B to the end of the temp
'string with a comma and a space
   For rw = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(rw, 1).Value = myCell Then
     tmpString = tmpString & Cells(rw, 1).Offset(0, 1) & ", "
    End If
   Next
'Strip off trailing comma and space and place result in cell
   MultString = Left(tmpString, Len(tmpString) - 2)
End Function


Report •

#3
January 10, 2011 at 07:30:59
This looks like it is close to what I need but I am wondering if you know how I should tweak it to fit my needs. In Column A I have a risk identification numbers, i.e risk 1, 2, 3, 4, etc. In column G I have the correspoding risk score (impact x probability). What I would like to do is create a risk map in which a cell will list all of the risk #'s (Column A values) if column G = a numerical value. Ex, what are all of the risks with a risk score of 7?

Report •

Related Solutions

#4
January 10, 2011 at 09:21:38
Try this modification.

Enter 7 in a cell, e.g. J1 and then enter =MultRisk(J1) in another cell.


Option Explicit
Function MultRisk(ByVal myCell As Range)
Dim rw As Integer
Dim tmpString As String
Application.Volatile
'Loop through Column G data
'If the value in a cell matches the value we are looking
'for, add the value in Column A to the end of the temp
'string with a comma and a space
   For rw = 1 To Cells(Rows.Count, 7).End(xlUp).Row
    If Cells(rw, 7).Value = myCell Then
     tmpString = tmpString & Cells(rw, 1) & ", "
    End If
   Next
'Strip off trailing comma and space and place result in cell
   MultRisk = Left(tmpString, Len(tmpString) - 2)
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question