VLookUp on multiple Rows with the same Value

[memuench]

Hello,
I am having Problems implement a VLookUp Function. I can't post the actual worksheet since it is confidential Company documents so I'll try to make a example here.

I got a Pivottable in Sheet 1 looking like this:

Col 1 Col 2 Col 3
ABB Development 5
ABB Product Support 10
ABB Beta Testing 15

Then I got a second Table in Sheet 2 looking like this:
Development Product Support Beta Testing
ABB

I think you already get the Point. I want the Values for the component ABB in the corresponding Category in Sheet 2. But here's the Problem: VLookUp will only search the first row with ABB. In Sheet 2 the Development Column always gets filled out with the correct number, but the rest doesn't since the Function obviously stops looking after finding the first row.

Anybody got an idea how to handle this?
Thanks

 

[jorismoerings]

Hi,

Take a look at the "GETPIVOTDATA function.
It will looks something like this:

See if this helps:

Book1
	A	B	C	D
6	Name	Type	Sum of Total
7	ABB	Beta Testing	15
8		Development	5
9		Product Support	10
10	ABB Total		30
11	Grand Total		30
12
13
14		Development	Product Support	Beta Testing
15	ABB	5	10	15
Sheet1

Cell Formulas
Range	Formula
B15	=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",B$14)
C15	=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",C$14)
D15	=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",D$14)

 

[memuench]

Hi,

Take a look at the "GETPIVOTDATA function.
It will looks something like this:

See if this helps:

	A	B	C	D
Name	Type	Sum of Total
ABB	Beta Testing
Development
Product Support
ABB Total
Grand Total
Development	Product Support	Beta Testing
ABB

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

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

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",B$14)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C15[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",C$14)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$6,"Name",$A15,"Type",D$14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I got a #REF Error. I used the following function:

=GETPIVOTDATA("Total";'[test.xlsx]testsheet'!$G$10;"Applic. area";A12709;"ProcOrg Cluster";"Product Support")

whereas Applic. Area is the Name in our example and A12709 the cell of the Name. ProcOrg Cluster is the Type and "Product Support" the word we are searching for

 

[jorismoerings]

Hi,

Means there's something wrong with the filled in fields and without looking at your file, i can't determine what.
I've linked my test model, take a look at my test model perhaps it helps you sort out what was wrong.

 

[memuench]

Hi,
It looks the same as yours, i'm trying it right now on an empty excel

 

[memuench]

Hi,

Means there's something wrong with the filled in fields and without looking at your file, i can't determine what.
I've linked my test model, take a look at my test model perhaps it helps you sort out what was wrong.

Hi Joris, I got it to work thanks to your formula.
Another question, I'm having a similar Problem now when wanting to extract data from a regular table (not a pivottable!). Is there a similar formula to what you posted since I can't use VLookUp (same values in rows again)

Thanks and BR

 

[jorismoerings]

Hi,

No there's no similar function to do so but there are more options than just using VLOOKUP.
If you can share a sample table and explain what your looking for. we can come up with a suitable solution for that as well.