Here's the VBA approach. Assuming Excel 2007 or later; first few steps will differ if you're using a pre-Ribbon version of Excel.
Enable the Developer Tab
Click on "Visual Basic" on the Developer Tab on the Ribbon
Right-click on "VBAProject (whatever-your-sheet-is-named.xls*)" and do Insert -> Module in the context menu
Paste in this code
Option Explicit
Function allquixotic(param As Variant, search As Range, values As Range, Optional absolute As Boolean = False) As String
Dim sep As String, retval As String
Dim i As Integer, rownum As Integer
Dim look As Range, j As Range
sep = ", "
retval = ""
For i = 1 To search.Rows.Count
Set look = search.Cells(i, 1)
If absolute Then
rownum = look.Row
Else
rownum = i
End If
If look.Value = param Then
If absolute Then
Set j = values.Worksheet.Cells(rownum, values.Column)
Else
Set j = values.Cells(i, 1)
End If
retval = IIf(retval = "", retval & j.Value, retval & sep & j.Value)
End If
Next
allquixotic = retval
End Function
Use the worksheet function (feel free to rename it) by using a formula like
=allquixotic(A1,$A$1:$A$15,$B$1:$B$15,true)
Use the fill handle to put the formula in all the cells
The parameters are the following:
=allquixotic(look_cell, key_range, value_range, absolute)
look_cell
: The first parameter, should be a single cell or a value literal. Valid input includes things like 3
, $6.25
, "Hello"
, etc. This is the value that you are trying to find in key_range
.
key_range
: This should be a range of cells (more than one cell); if absolute
is true then you will get very strange results unless this is a contiguous range (all the values are in sequential rows).
value_range
: This should be a range of cells (more than one cell); if absolute
is true then you will get very strange results unless this is a contiguous range (all the values are in sequential rows).
absolute
: If true, then we will use the absolute row number (relative to the number of rows in the entire spreadsheet) of each "found" row in the key_range
to determine what row to extract a value from value_range
on. If false, we will use relative numbers; for instance, if we find a match in the third row of key_range
, then we will extract the value from the third row of value_range
. Recommended value is FALSE, or you can omit it to default to that.
Note: This function does not support the case where the key and value ranges are in columns, but it should be fairly easy to adapt it to that.
Also, if you specify multiple columns in either the key_range or the value_range, only the leftmost column will be used.