Vlookup or Index for multiple rows?

p45cal · #5 10-09-2018, 07:07 AM

The "raw data" you supply isn't raw data but never mind. There are problems with this; some dates aren't dates at all.

In the attached in the final result sheet, in cell J4, your stated requirement (highlighted in yellow).

In K4, a formula:

Code:

=TEXTJOIN(", ",TRUE,IF((('Contracts by Supplier raw data'!$B$3:$B$6=B4)*('Contracts by Supplier raw data'!$C$3:$C$6=C4)*('Contracts by Supplier raw data'!$D$3:$D$6=D4))=1,'Contracts by Supplier raw data'!$AH$3:$AH$6))

but since I suspect you have Excel 2013 the TextJoin function is unavailable. This returns duplicate years.
In cell L4 a formula:

Code:

=TxtJn('Contracts by Supplier raw data'!$B$3:$B$6,B4,'Contracts by Supplier raw data'!$C$3:$C$6,C4,'Contracts by Supplier raw data'!$D$3:$D$6,D4,'Contracts by Supplier raw data'!$AH$3:$AH$6)

This is a user-defined function the code of which (only for interest) is:

Code:

Function TxtJn(Rng1, Crit1, Rng2, Crit2, Rng3, Crit3, RngToJoin)
Rng1Vals = Rng1.Value
Rng2Vals = Rng2.Value
Rng3Vals = Rng3.Value
Crit1Val = UCase(Crit1.Value)
Crit2Val = UCase(Crit2.Value)
Crit3Val = UCase(Crit3.Value)
RngToJoinVals = RngToJoin.Value
uniques:
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(RngToJoinVals)
  If UCase(Rng1Vals(i, 1)) = Crit1Val And UCase(Rng2Vals(i, 1)) = Crit2Val And UCase(Rng3Vals(i, 1)) = Crit3Val Then
    d(RngToJoinVals(i, 1)) = 1
  End If
Next i
myAry = d.keys
'sort:
For i = LBound(myAry) To UBound(myAry) - 1
  For j = i + 1 To UBound(myAry)
    If myAry(i) > myAry(j) Then
      Temp = myAry(j): myAry(j) = myAry(i): myAry(i) = Temp
    End If
  Next j
Next i
TxtJn = Join(myAry, ", ")
 End Function

No duplicates and in ascending order.

There will be some built-in-worksheet-functions-only formulae that could be concocted but they'll be long and complicated and vey difficult to maintain and understand. Although the code for the user-defined function I've suggested isn't simple, the use of the user-defined function on a sheet is relatively straightforward.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VLookup or Index Multiple Tables	mycon73	Excel	4	07-27-2018 06:53 AM
Index/match, Vlookup?	kelwea	Excel	1	01-24-2017 10:19 AM
Can I substitute Vlookup with Index	paxon	Excel	4	03-10-2016 07:03 AM
Match Index with sumproduct/vlookup	angie.chang	Excel	1	06-18-2012 08:47 AM
Vlookup or Index/Match - Multiple Criteria	ruci1225	Excel	1	01-15-2012 07:31 AM