Vlookup for Object with subtotals

[mfowler]

I'm trying to look-up the sub-total of sheet 1, Fund 7000107000(which is an object) from the pink cell of sheet 2.

What formula can I use?


Sheet 1
<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=386 border=0 x:str><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #b8d7e4" width=125 height=21 x:str="'Fund">Fund</TD><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 102pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #b8d7e4" width=136 x:str="'Order">Order</TD><TD class=xl54 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:num="4000200">4000200</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21 x:str="'7000107000">7000107000</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A76001999900">A76001999900</TD><TD class=xl55 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="4199">$ 4,199.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A78001999900">A78001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A82001999900">A82001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A84001999900">A84001999900</TD><TD class=xl55 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="6070.99">$ 6,070.99</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A86001999900">A86001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A87001999900">A87001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A88001999900">A88001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl53 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" x:str="'Result">Result</TD><TD class=xl57 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" align=right x:num="10269.99">$ 10,269.99</TD></TR></TBODY></TABLE>

Sheet 2
<TABLE style="WIDTH: 469pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=623 border=0 x:str><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 113pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #b8d7e4" width=150 height=21 x:str="'Fund\G/L Account">Fund\G/L Account</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:str="'1300500">1300500</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 89pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=118 x:str="'1300555">1300555</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 79pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=105 x:str="'1300599">1300599</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:str="'4000200">4000200</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #b8d7e4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21 x:str="'7000107000">7000107000</TD><TD class=xl54 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="-10269.99">-$ 10,269.99</TD><TD class=xl55 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff"></TD><TD class=xl53 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD><TD class=xl56 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #ff9999" x:num="10269.99">$ 10,269.99</TD></TR></TBODY></TABLE>

 

[crimson_b1ade]

=VLOOKUP("Result",INDIRECT("Sheet1!B"&MATCH(A2,Sheet1!A:A,0)&":C65000"),2,FALSE)

Used the MATCH function to find the first instance of the lookup value "7000107000" then used the INDIRECT function in conjunction with the VLOOKUP function to lookup the word "Result". The starting row of the lookup range in the VLOOKUP formula will be the row that is returned by the MATCH function. The ending row was just 65000, which I used just so I wouldn't have to keep adjusting the formula (note: Excel 2007 now has over a million rows whereas pre 2007 had only 65,536 rows).

 

[mfowler]

crimson_b1ade

maybe I'm just dingy, but can't get it to work. Here it is in a little more detail.


Sheet name: 4000200 Reconciliation

Column B
<TABLE style="WIDTH: 298pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=397 border=0 x:str><COLGROUP><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 8pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=11 height=21></TD><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 94pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #b8d7e4" width=125 x:str="'Fund">Fund</TD><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 102pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #b8d7e4" width=136 x:str="'Order">Order</TD><TD class=xl54 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:num="4000200">4000200</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'7000107000">7000107000</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A76001999900">A76001999900</TD><TD class=xl55 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="4199">$ 4,199.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A78001999900">A78001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A82001999900">A82001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A84001999900">A84001999900</TD><TD class=xl55 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="6070.99">$ 6,070.99</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A86001999900">A86001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A87001999900">A87001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A88001999900">A88001999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl53 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" x:str="'Result">Result</TD><TD class=xl57 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" align=right x:num="10269.99">$ 10,269.99</TD></TR></TBODY></TABLE>



Sheet name:GL Reconciliation

Column B
<TABLE style="WIDTH: 477pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=634 border=0 x:str><COLGROUP><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 402" width=11><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 8pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=11 height=21>

</TD><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 113pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #b8d7e4" width=150 x:str="'Fund\G/L Account">Fund\G/L Account</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:str="'1300500">1300500</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 89pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=118 x:str="'1300555">1300555</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 79pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=105 x:str="'1300599">1300599</TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" width=125 x:str="'4000200">4000200</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl51 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #b8d7e4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'7000107000">7000107000</TD><TD class=xl54 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="-10269.99">-$ 10,269.99</TD><TD class=xl55 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff"></TD><TD class=xl53 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="0">$0.00</TD><TD class=xl56 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #ff9999" x:num="10269.99">$ 10,269.99</TD></TR></TBODY></TABLE>


Note: '7000107000 is an object

 

[crimson_b1ade]

why do you keep saying " '7000107000 is an object "? Aren't all items in a spreadsheet some sort of 'object'? What kind of object are you referring to (a combo-box, text box, cell etc...etc..)?

Can you post back your attempt of the formula...

 

[mfowler]

I thought(and I may be wrong) that vlookup was for numeric values and hlookup was for objects, or anything non-numeric. This report is pulled from a database, and the "Fund" starts with a hypen, therefore non-numeric????

After entering the formula, the cell defaulted to, "#NAME?".

 

[crimson_b1ade]

vlookup is for vertical lookups (hence the "v")
hlookup is for horizontal lookups (hence the "h")
both can lookup numeric or non-numeric values.

here's the formula based on the additional information you posted:

=VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B7,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE)

 

[mfowler]

It worked great. You're a genious!

I took care of "if the fund wasn't listed" by an IF(ISERROR

But how do I get it to pick up the funds that don't have a "Result" subtotal?

IE
<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=386 border=0 x:str><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl55 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776 0.5pt solid; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ff9999" width=125 height=21 x:str="'7073307000">7073307000</TD><TD class=xl55 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 102pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #ff9999" width=136 x:str="'A82703999900">A82703999900</TD><TD class=xl56 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776 0.5pt solid; BORDER-LEFT: #4d6776; WIDTH: 94pt; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #ff9999" width=125 x:num="0">$100.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21 x:str="'7073308000">7073308000</TD><TD class=xl51 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A82703999900">A82703999900</TD><TD class=xl53 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="12000">$12,000.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl51 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #d7ecf4" x:str="'A92703999900">A92703999900</TD><TD class=xl53 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #eef9ff" x:num="2500">$2,500.00</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl51 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776 0.5pt solid; BORDER-BOTTOM: #4d6776 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #d7ecf4" height=21></TD><TD class=xl52 style="BORDER-RIGHT: #4d6776 0.5pt solid; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" x:str="'Result">Result</TD><TD class=xl54 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #4d6776; BORDER-LEFT: #4d6776; BORDER-BOTTOM: #4d6776 0.5pt solid; BACKGROUND-COLOR: #fefeb8" align=right x:num="14500" x:fmla="=SUM(C2:C3)">$14,500.00</TD></TR></TBODY></TABLE>

Here's where I ended up in the formula:

=IF(ISERROR(VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE)),"0",VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE))

 

[crimson_b1ade]

=IF(ISBLANK(INDEX('4000200 Reconciliation'!B:D,MATCH(B8,'4000200 Reconciliation'!B:B,0)+1,1))=TRUE,INDEX('4000200 Reconciliation'!B:D,MATCH(B8,'4000200 Reconciliation'!B:B,0),3),"Your previous formula goes here!")

your formula would go into the latter section of the above IF statement, where is says "Your previous formula goes here!". Put in the IF(ISERROR formula you created.

Essentially what it does is it checks to see if there is a fund number directly below the fund number your looking up. If there is, then there is no subtotal so take the total from the same row, if there isn't, then it must be a subtotal so lookup "Result" and put in the adjacent subtotal number.

 

[mfowler]

It doesn't like "Result" for some reason.

=IF(ISBLANK(INDEX('4000200 Reconciliation'!B:D,MATCH(B20,'4000200 Reconciliation'!B:B,0)+1,1))=TRUE,INDEX('4000200 Reconciliation'!B:D,MATCH(B20,'4000200 Reconciliation'!B:B,0),3),"=VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE)")

 

[crimson_b1ade]

This (now focus your attention on the purple)...

"=VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE)"

should be this...

VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE)

Like so...

=IF(ISBLANK(INDEX('4000200 Reconciliation'!B:D,MATCH(B20,'4000200 Reconciliation'!B:B,0)+1,1))=TRUE,INDEX('4000200 Reconciliation'!B:D,MATCH(B20,'4000200 Reconciliation'!B:B,0),3),VLOOKUP("Result",INDIRECT("'4000200 Reconciliation'!C"&MATCH(B20,'4000200 Reconciliation'!B:B,0)&":D65000"),2,FALSE))