Hey all... I hope this comes across ok. I have a utility that exports data
out to an Excel file (using Excel '03) via SQL statements. My query is fine
and returns 19161 rows. I have this data in a tab called MainQueryResult.
This table is sorted on the column I will ultimately perform the VLOOKUP.
Let's call this column "ID" and it's in Column B of that tab. Column C we'll
call "Name". In this sheet, there are multiple instances of that ID, so
sorted, they are all grouped together.

I have another query that returns a distinct list of IDs. Let's call this
sheet "Data". It's in column A of data, with a header in A1 and the first
datapoint in A2...all the way through A1050. The SQL query that outputs
these distinct IDs is correct and works fine... I am able to have the
utility output excel functions as row values and have used this successfully,
however, I will need to make the function lookup-address-independent.

Let's say MainQueryResult looks something like this (column header "ID" is
B1):

ID NAME
7 Bob
7 Bob
7 Bob
8 Joe
8 Joe
8 Joe
9 Zoe
9 Zoe
....etc.

My "Data" tab looks like this:
ID NAME
7 this is cell B2
8 this is cell B3
9 this is cell B4

I need to enter the same function in cells B2, B3, and B4 as the SQL will be:
select distinct ITEM_ID as "ID", 'insert function here' as "NAME"
from...blah blah blah

One more tab: There is a cell that contains the count of rows returned in
MainQueryResult.

My SQL is good except for the insert function here part.

In Excel, if I manually enter the following formula:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$19162,2)

I get the correct value of Bob, however, I need to make this address
independent.
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

By all rights, this should work. I ran through the formula evaluator and
discovered the following:
First Evaluation (note the first address function):
=VLOOKUP(INDIRECT(ADDRESS(2, 1)),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Second Evaluation (note the first indirect function):
=VLOOKUP(INDIRECT("$A$2"),
MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1), 3)),2)

Third Evaluation (note the first indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((Count_Comp! $A$2 + 1),
3)),2)

Fourth Evaluation (note the value from Count_Comp!$A$2 is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19161 + 1), 3)),2)

Fifth Evaluation (note the addition inside the address function is now
eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS((19162), 3)),2)

Sixth Evaluation (note the parenthesis around 19162 are removed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT(ADDRESS(19162, 3)),2)

Seventh Evaluation (note the address function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:INDIRECT("$C$19162"),2)

Eigth Evaluation (note the indirect function is now eval'ed):
=VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Before we continue, recall the formula I keyed in manually:
=VLOOKUP(a2, MainQueryResult!$B$2:$C$$19162,2)

a2 eval's to 7...

8th: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)
Man: =VLOOKUP(7, MainQueryResult!$B$2:$C$19162,2)

Wow...they're the same.... Continuing to eval the formula:

Ninth Evaluation (note the array is now eval'ed):
=VLOOKUP(7, #VALUE!,2)

VLOOKUP evaluates out to #VALUE!.

I'm not sure what's causing the problem here. I entered some dummy values
into column B on "Data" to come up with the correct structure...which
worked... All I changed was adding a tab reference (which I've double and
triple checked), MainQueryResult! and changing the count reference to
Count_Comp! instead of the count of distinct IDs.... either way, those
changes I'm pretty sure of as they evaluate out to what I entered manually.

Any ideas here?

Thanks.