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>
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,108
Messages
5,599,760
Members
414,336
Latest member
Nicolas2465

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