VLOOKUP - referencing the "table array"

[canyflyer]

As you all would most probably know the formula in VLOOKUP has a table array variable.
I have an excel worksheet for vehicle agreements. it has a main Sheet for interest values with the agreements numbers running along the left hand side and the dates running along the top.
Each agreement has its own sheet that contains the details of its interest and installments.

Now what I'm trying to do is update the main Interest sheet with the details from the various vehicle agreements sheets.
<TABLE style="WIDTH: 145pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19 width=86></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl71 width=106>INTEREST</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69>2008/07/26</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20> </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>TOTAL</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19>Agreement #</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 823 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 824 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 825 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 826 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 827 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 828 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 829 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19> 830 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR></TBODY></TABLE>
so i need to tell VLOOKUP ("Date","Sheet with the name of the agreement #" "Table array", "Column #", etc)
so how do I make the table array look in the sheet in the Agreement # column?

 

[Darren Bartrup]

Edit: Oh, and welcome to the board!

I'd suggest having a look at the ADDRESS and INDIRECT functions.

Use a combination of these functions to return a reference to the table you need.

 

[CharlesChuckieCharles]

As you all would most probably know the formula in VLOOKUP has a table array variable.
I have an excel worksheet for vehicle agreements. it has a main Sheet for interest values with the agreements numbers running along the left hand side and the dates running along the top.
Each agreement has its own sheet that contains the details of its interest and installments.

Now what I'm trying to do is update the main Interest sheet with the details from the various vehicle agreements sheets.
<TABLE style="WIDTH: 145pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19 width=86></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl71 width=106>INTEREST</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl69>2008/07/26</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>TOTAL</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl64 height=19>Agreement #</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>823 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>824 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>825 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>826 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>827 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>828 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>829 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #ccffcc; HEIGHT: 14.4pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 height=19>830 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68></TD></TR></TBODY></TABLE>
so i need to tell VLOOKUP ("Date","Sheet with the name of the agreement #" "Table array", "Column #", etc)
so how do I make the table array look in the sheet in the Agreement # column?

HLOOKUP would allow you to look acroos the sheet on the date column
the column no becomes row no in HLOOKUP

but you can't do negative columns or rows in V/H Lookup

maybe MATCH address and indirect
or possibly pivot/transpose your data first

I would really need to see some of your spreadsheet

 

[Darren Bartrup]

From what I can make out, you'll have a sheet called "823", "824", etc.

In each of these sheets you'll have the date (2008/07/26) running down the left column, with the actual figure you're after in a table to the right of this (so VLOOKUP will work).

So I'm thinking a formula something like this should work:

=VLOOKUP(B2,OFFSET(INDIRECT(ADDRESS(1,1,,,A3)),,,5,3),2,FALSE)

Taking it apart:
ADDRESS(1,1,,,A3)
If you have 823 in cell A3 this will return the string:'823'!$A$1

INDIRECT(ADDRESS(1,1,,,A3) will turn the string into an actual reference.

OFFSET(INDIRECT(ADDRESS(1,1,,,A3)),,,5,3) will extend the reference down 5 rows and across 3 columns giving a reference of '823'!$A$1:$C$5

This can then be used as your table array reference in your VLOOKUP.
VLOOKUP(B2,'823'!$A$1:$C$5,2,FALSE)

 

[canyflyer]

Thanks for the reply!!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Now say I want to use the Offset and Indirect address for a sum?<o></o>
I tried altering your formula with SUM but it didn't work.<o></o>
So let’s say every sheet has a set of numbers from B14:B24 and i want the sum of those numbers collated on a separate sheet per agreement - how can i make the SUM function go to each sheet that i reference it to via the offset and Indirect Address and sum the numbers in B14:B24?<o></o>
Thanks!!

 

[Darren Bartrup]

There's two ways I can think of to do it:

With the target sheet name in cell A1 you could use:

=SUM(INDIRECT("'" & $A$1 & "'!B14:B24"))

or you could use:

=SUM(OFFSET(INDIRECT(ADDRESS(14,2,,,"Sheet2")),,,11))

(again you could replace "Sheet2" above with a cell reference.