Vlookup for Object with subtotals

mfowler

New Member
Joined
Mar 30, 2009
Messages
8
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>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
=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

New Member
Joined
Mar 30, 2009
Messages
8
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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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

New Member
Joined
Mar 30, 2009
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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

New Member
Joined
Mar 30, 2009
Messages
8

ADVERTISEMENT

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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
=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

New Member
Joined
Mar 30, 2009
Messages
8
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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,707
Messages
5,833,241
Members
430,199
Latest member
Petty queen

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