Vlookup or Index , Match to display multiple Rows of data

[acidburn786]

I am Trying to Report on some information based on 2 selections.

Selection 1 = Company
Selection 2 = Process

Each process consist of a number of Activities (1 up to 25) Rows


Data is in sheet1

Lookup in sheet2

So when I select, the Company and a process
Activities should be displayed under each other.
The time it takes for each activity will depend on which Company I selected

Sheet1

*	A	B	C	D	E
*	*	*	*
Process 1	*
Process 1
Process 1	*
Process 1
Process 1	*
Process 2	*
Process 2
Process 3	*	*
Process 4	*
Process 4	*
Process 4	*	*	*
*	*	*	*	*
*	*	*	*
*	*	*	*	*
Process selected:	Company selected:	*
*	*	*	*	*
Activities	Period	*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:128px;"><col style="width:204px;"><col style="width:194px;"><col style="width:133px;"><col style="width:202px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffcc00"]sheet1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0"]Process[/TD]
[TD="bgcolor: #c0c0c0, align: left"]SLA[/TD]
[TD="bgcolor: #c0c0c0"]Company A[/TD]
[TD="bgcolor: #c0c0c0"]Company B[/TD]
[TD="bgcolor: #c0c0c0"]Company C[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: left"]Activity 1[/TD]
[TD="bgcolor: #ff99cc, align: center"]25[/TD]

[TD="bgcolor: #ff99cc, align: center"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: left"]Activity 2[/TD]
[TD="align: center"]None[/TD]
[TD="align: center"]n/a[/TD]
[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: left"]Activity 3[/TD]
[TD="bgcolor: #ff99cc, align: center"]Prior to payment of commission[/TD]

[TD="bgcolor: #ff99cc, align: center"]Prior to payment of Commission[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: left"]Activity 4[/TD]
[TD="align: center"]N/a[/TD]
[TD="align: center"]n/a[/TD]
[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: left"]Activity 5[/TD]
[TD="align: center"]N/a[/TD]

[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: left"]Activity 1[/TD]
[TD="align: center"]N/a[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: left"]Activity 2[/TD]
[TD="align: center"]N/a[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: left"]Activity 1[/TD]
[TD="align: center"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: left"]Activity 1[/TD]
[TD="align: center"]45[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: left"]Activity 2[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: left"]Activity 3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]
[TD="bgcolor: #000000"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #ffcc00"]sheet2[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #ffff00"]Process 1[/TD]

[TD="bgcolor: #ffff00"]Company A[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: left"]Activity 1[/TD]
[TD="bgcolor: #ff99cc, align: center"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: left"]Activity 2[/TD]
[TD="align: center"]None[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: left"]Activity 3[/TD]
[TD="bgcolor: #ff99cc"]Prior to payment of commission[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: left"]Activity 4[/TD]
[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: left"]Activity 5[/TD]
[TD="align: center"]N/a[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

So in the Example above. A21:B25 should be formulae that pul data in

 

[t0azt]

In B21

=IFERROR(INDEX($C$2:$E$12,MATCH($B$18&A21,$A$2:$A$13&$B$2:$B$13,0),MATCH($D$18,$C$2:$E$2,0)),"")

Entered with CTRL+SHIFT+ENTER
This formula will leave the activities that are not included in a process (like activities 3-5 for process 2) blank

 

[acidburn786]

In B21

=IFERROR(INDEX($C$2:$E$12,MATCH($B$18&A21,$A$2:$A$13&$B$2:$B$13,0),MATCH($D$18,$C$2:$E$2,0)),"")

Entered with CTRL+SHIFT+ENTER
This formula will leave the activities that are not included in a process (like activities 3-5 for process 2) blank

This works to pull the data in thanks, However I need to pull in the Activities also based on the Process (B18)

In other words I do not want to pre-populate A21 - A25, as there may be a list of 25 Activities. This should also be a formula which is based on the process selected (B18) & company selected (D18)

Thanks so far.

 

[t0azt]

Ok, try this in A21, again entered with CTRL+SHIFT+ENTER and drag down

=IFERROR(INDEX($B$3:$B$13,MATCH(0,SI($B$18=$A$3:$A$13,COUNTIF($A$20:$A20,$B$3:$B$13),""),0)),"")

 

[acidburn786]

Ok, try this in A21, again entered with CTRL+SHIFT+ENTER and drag down

=IFERROR(INDEX($B$3:$B$13,MATCH(0,SI($B$18=$A$3:$A$13,COUNTIF($A$20:$A20,$B$3:$B$13),""),0)),"")

Something here don't seem right?

=IFERROR(INDEX($B$3:$B$13,MATCH(0,SI($B$18=$A$3:$A$13,COUNTIF($A$20:$A20,$B$3:$B$13),""),0)),"")

 

[t0azt]

sorry i use the spanish version of excel because im in mexico and have to translate them manually. just change the "si" to IF

 

[acidburn786]

sorry i use the spanish version of excel because im in mexico and have to translate them manually. just change the "si" to IF

Gosh... never knew the formulas will differ across languages.

Looks like that Fixed it

Thanks Alot