Vlookup or Index for multiple rows?

paxon · #1 10-01-2018, 05:31 AM

Hi;

with vlookup i can only get the 1st value that the formula found. in this case is only 2017.

But what formula can I use if I want to show that there are several contracts expiring for the Client in sheet2?

Ideally in sheet 1 column J I would like to see 2011, 2017, 2018 as there are multiple expiration date.
(I have added manually in sheet1 column J4 the result I would like to see with the formula)

Thanks

p45cal · #2 10-01-2018, 05:41 AM

The tables on all the sheets in your sample workbook appear to be derived from pivot tables.
It's more likely we can help better with the source data for those pivot tables, desensitised if necessary.

Pecoflyer · #3 10-01-2018, 09:17 AM

Or https://www.ablebits.com/office-addi...-values-excel/

paxon · #4 10-09-2018, 02:19 AM

Hi This is the further data from Pivot table (see tab Pivot contract expiration).

you will see that in Pivot table I have contract starting in 2011-2017-2018. I would be interested in having the contract expiration date in tab "final result" column "J4".

With Vlokkup is only taking the 1st value which is finding 2017, how can i include the 2018 and 2011?

Thanks

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.

paxon · #6 10-11-2018, 08:10 AM

Thanks for your great help!

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