Can't combine multi-criteria lookup that also displays multiple results in one cell

[klk252]

Hi there,

I'm looking for some help on combining formulas to do a multi-criteria lookup (vlookup or index/match) that can display multiple results in one cell.

So far, I've gotten separate formulas to work - i can do a multi-criteria lookup with only on result shown, and can do a single-criteria lookup with multiple results shown ... but have been trying desperately to figure out how to combine the two.

-------------------------------------------------
Here is my data:

A B C D
SqFt Volts Type Options
10 120 Wire Cat-120-140
10 120 Mat Dog-120-140
14 120 Wire Cat-120-140
14 120 Wire Cat-120-210
15 120 Wire Cat-120-210
15 120 Mat Dog-120-210
20 120 Wire Cat-120-210
20 120 Wire Cat-120-280
20 120 Mat Dog-120-280

Lookup Criteria
C14 = 20 (sq ft)
C15 = 120 (volt)
C16 = Wire (type)

--------------------------------------------------
Here are my formula's so far:

Multi-criteria lookup with single result:
=INDEX($D$2:$D$10,MATCH(1,(C14=$A$2:$A$10)*(C15=$B$2:$B$10)*(C16=$C$2:$C$10),0),1) ... which returns "Cat-120-210" ... but thats only one possible result, as "Cat-120-280" is also a possibility.


Single-criteria lookup with multiple results in one cell:
=MULTIVLOOKUP((C14),A2:D10,3) .... which returns "Cat-120-210, Cat-120-280, Dog-120-280" but only against the 20 sqft criteria

Public Function MultiVLookup(MatchWith As String, TRange As Range, col_index_num As Integer)
MatchWith = LCase$(MatchWith)
If (MatchWith = "") Then
MultiVLookup = ""
Else
For Each cell In TRange
If LCase$(cell.Value) = MatchWith Then
x = x & cell.Offset(0, col_index_num).Value & ", "
End If
Next cell
If (x = "") Then
MultiVLookup = ""
Else
MultiVLookup = Left(x, Len(x) - 2)
End If
End If
End Function

-------------------------------------

Is there any way to combine both formulas?? I've been trying to figure it out for 3 days without luck ... turning to the experts for help

See my attached spreadsheet.

[Andy Pope]

here is one way using helper cells to build up matching items.