VLookUp on multiple Rows with the same Value

memuench

New Member
Joined
Jan 19, 2018
Messages
4
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi,

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

See if this helps:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Name</td><td style=";">Type</td><td style=";">Sum of Total</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">ABB</td><td style=";">Beta Testing</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">Development</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">Product Support</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">ABB Total</td><td style="text-align: right;;"></td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Grand Total</td><td style="text-align: right;;"></td><td style="text-align: right;;">30</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">Development</td><td style=";">Product Support</td><td style=";">Beta Testing</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">ABB</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;">15</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B15</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Total",$A$6,"Name",$A15,"Type",B$14</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C15</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Total",$A$6,"Name",$A15,"Type",C$14</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D15</th><td style="text-align:left">=GETPIVOTDATA(<font color="Blue">"Total",$A$6,"Name",$A15,"Type",D$14</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

memuench

New Member
Joined
Jan 19, 2018
Messages
4
Hi,

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

See if this helps:

ABCD
6NameTypeSum of Total
7ABBBeta Testing15
8Development5
9Product Support10
10ABB Total30
11Grand Total30
12
13
14DevelopmentProduct SupportBeta Testing
15ABB51015

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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.
 
Last edited:

memuench

New Member
Joined
Jan 19, 2018
Messages
4

ADVERTISEMENT

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

memuench

New Member
Joined
Jan 19, 2018
Messages
4
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

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,123
Messages
5,599,834
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top