Using VLookup with multiple search criteria

[joegio2504]

What I have is a pivot table that looks something like this
A B C

<TABLE style="WIDTH: 216pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=287 border=0><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl74 id=td_post_274094 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; WIDTH: 93pt; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 30pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=124 height=40>Product</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; WIDTH: 94pt; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=125>Work Centers</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; WIDTH: 29pt; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" width=38>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>111.32</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>21081102</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">WC-10</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">WC-20</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">WC-30</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">WC-40</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">WC-50</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">WC-60</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">(blank)</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none">(blank)</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>2115</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>21081102 Total</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: white; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>2115</TD></TR></TBODY></TABLE>

"WC" Stands for work center. I want to use a VLookup in column D,E, & F that will look up the work center information i.e. description, units per hour, and cost center. In order to do so, it needs the following criteria: Product name located in A2 (111.32), and SKU # located in B3 (21081102). How would I do a vlookup, with those two criterias in order to find all my work center information? Also keep in mind, this code has to be dynamic since its based on a pivot table. i.e those two sets of criteria could be located in different cells depending on which product and sku combination is used. Some use 6 work centers, some use 10... FYI


And on a side note, is there anyway of having my pivot table not show those (blank) fields?

Thanks guys and gals!

 

[Trevor G]

Part answer to remove blanks, have you tried this

Right click on the "Field" that shows the word "(Blank)" and select "Field Setting". Now in the "PivotTable Field" dialog tick "Show Items With No Data". Click Ok.