Vlookup, Indirect, Excel 2007 Table, Different Worksheets

[IntlTaxMan]

I've got a workbook with tables on multiple worksheets. The worksheets are called Summary, Q1, Q2, Q3, and Q4. Each of the worksheets have tables:


<TABLE style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; BORDER-COLLAPSE: collapse; DIRECTION: ltr; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid" border=1 cellSpacing=0 cellPadding=0 valign="top"><TBODY><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Worksheet

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Table

</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Summary

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">SummaryTable

</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q1

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q1Table

</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q2

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q2Table

</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q3

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q3Table

</TD></TR><TR><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 0.919in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q4

</TD><TD style="BORDER-BOTTOM: #a3a3a3 1pt solid; BORDER-LEFT: #a3a3a3 1pt solid; PADDING-BOTTOM: 4pt; PADDING-LEFT: 4pt; WIDTH: 1.178in; PADDING-RIGHT: 4pt; VERTICAL-ALIGN: top; BORDER-TOP: #a3a3a3 1pt solid; BORDER-RIGHT: #a3a3a3 1pt solid; PADDING-TOP: 4pt">Q4Table

</TD></TR></TBODY></TABLE>

I have a column (LookupColumn) in Summary:SummaryTable that I'd like to refer to the corresponding table on one of the worksheets based on lookup data in that row in SummaryTable (e.g., an event that occurs in Q1, Q2, Q3, or Q4).

I've tried INDIRECT and concatenation within the Vlookup formula but can't get it to work. And there isn't much info on formulas and tables out there so I'm at a loss. I'm also wondering given the issues Vlookup has with tables that cross worksheet INDIRECTs may not work.

Any thoughts / comments / suggestions would be appreciated.

Kind regards.
IntlTaxMan

 

[dave3009]

What INDIRECT did you try, I often get the syntax wrong and this might be handy to know

 

[swinglow]

can you be a little more explicit in what you need? an example of the data and what you want to look up would be helpful

 

[IntlTaxMan]

Well, as I created an example to explain the issue, I figured out the issue. But in the interest of knowledge sharing (especially given the dearth of postings on Excel Tables) and just in case this starts a dialogue on better solutions, I'm posting my solution to the problem. And as one of the respondents correctly assumed - it was a formatting issue on an INDIRECT.


Quick recap:

• 5 tables on 5 worksheets (although arguably all tables could be on one worksheet).
• Data is received quarterly and a new table is created for each quarter. The reason - ID's may be the same across quarters, but amounts will be different.
• By inputting ID and Quarter info on the SummaryTable, users can pull data from any quarter.

<TABLE style="WIDTH: 294pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=391><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 125pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 width=166 colSpan=2>SummaryTable</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=97></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 30pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" height=40>ID</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none">Quarter</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 73pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl65 width=97>Concatenate ID / Qtr</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none">Table</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none">Lookup</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q1A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q1Table</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>1000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q1A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q1Table</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>2000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q2B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q2Table</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q2C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q2Table</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>50</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=226><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>Q1Table</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 74pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=98>Q1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 30pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" height=40>ID</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 74pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl65 width=98>Concatenate ID / Qtr</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none">Lookup Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q1A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>1000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q1A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>2000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q1B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>1400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q1C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>2500</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 183pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=244><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>Q2Table</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 87pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=116>Q2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64></TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 30pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" height=40>ID</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 87pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none" class=xl65 width=116>Concatenate ID / Qtr</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #4F81BD none; text-underline-style: none; text-line-through: none">Lookup Data</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q2A1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q2A2</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" height=20>B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none">Q2B1</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #B8CCE4 none; text-underline-style: none; text-line-through: none" align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" height=20>C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none">Q2C3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; mso-pattern: #DBE5F1 none; text-underline-style: none; text-line-through: none" align=right>50</TD></TR></TBODY></TABLE>



Formulas in SummaryTable:

• Original formulas in the Lookup column, rows 1 and 2 -
  • INDEX(Q1Table,MATCH(SummaryTable[[#This Row],[Concatenate ID / Qtr]],Q1Table[Concatenate ID / Qtr],0),3)
• Original formulas in the Lookup column, rows 3 and 4:
  • INDEX(Q2Table,MATCH(SummaryTable[[#This Row],[Concatenate ID / Qtr]],Q2Table[Concatenate ID / Qtr],0),3)
• I'm using INDEX / MATCH since in my real life example the lookup data is both right and left of the lookup value.
• I'm using concatenation in columns Concatenate ID / Qtr and Table
• I'm also using an IFERROR on the Lookup column (not shown) in case the user picks an ID / Quarter combination that doesn't exist.

My solution formula in the Lookup column (all rows):

• INDEX(INDIRECT(SummaryTable[[#This Row],
  ]),MATCH(SummaryTable[[#This Row],[Concatenate ID / Qtr]],INDIRECT(SummaryTable[[#This Row],

  ]&"[Concatenate ID / Qtr]"),0),3)
  

As I should know by now - be careful with your INDIRECT formatting.

Any comments are apprecated.

Kind regards.
IntlTaxMan

 

[IntlTaxMan]

But yet another question related to this issue. I also have a Vlookup in addition to the INDEX / MATCH reference to the tables. So if I change my reference in SummaryTable:Lookup to:


VLOOKUP(SummaryTable[[#This Row],[Concatenate ID / Qtr]],Q1Table[[Concatenate ID / Qtr]:[Lookup Data]],2,)

And then decide to change the Vlookup table_array to an INDIRECT, I #VALUE out.



VLOOKUP(SummaryTable[[#This Row],[Concatenate ID / Qtr]],INDIRECT(SummaryTable[[#This Row],

]&"[[Concatenate ID / Qtr]:[Lookup Data]]",2,)

Anybody see the error in the INDIRECT contained in the Vlookup?


Thanks in advance.

Kind regards.
Tim

 

[swinglow]

insert a parenthesis where the red one is:

VLOOKUP(SummaryTable[[#This Row],[Concatenate ID / Qtr]],INDIRECT(SummaryTable[[#This Row],

]&"[[Concatenate ID / Qtr]:[Lookup Data]]" ) ,2,)

 

[IntlTaxMan]

Thanks swinglow for the feedback...and the right solution.

What's funny is that when I typed in my formula last time I accidentally fixed one of my two errors in the formula.

Here was the original BAD formula in my XLS:

VLOOKUP(SummaryTable4[[#This Row],[Concatenate ID / Qtr]],INDIRECT(SummaryTable4[[#This Row],

]

) &"[Concatenate ID / Qtr]:[Lookup Data]",2,)

The two errors: 1. I had a parentheses in the wrong place as highlighted above (but didn't type it in the previous post), and 2. I typed double brackets around [Concatenate ID / Qtr]:[Lookup Data], but only had single brackets in my formula. So the correct formula as swinglow noted in the previous post:

VLOOKUP(SummaryTable4[[#This Row],[Concatenate ID / Qtr]],INDIRECT(SummaryTable4[[#This Row],

]&"[[Concatenate ID / Qtr]:[Lookup Data]]"),2,)

Thanks again swinglow - good eyes and great catch. Kind regards. IntlTaxMan