That worked perfectly, thanks.

In regards to your question, I am not using A2 because I'm going to be
outputting over 1000 rows and the SQL is much more complex to output A2, A3,
A4...etc in the results than it is to use ADDRESS(row(), 1). My SQL will be:

select ID, '=vlookup(indirect(address(row(), 1)), .... +1), 3, )),2)' as
"NAME" from blah where....etc.

That will ensure that I am getting the correct address reference for each row.

"Sheeloo" wrote:

Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A $2 + 1), 3)),2)

Second parameter has to be a reference... your formula is returning a
string... remember that the whole point of INDIRECT ... is to convert a
string to a reference... that is why you need to wrap your string building
formula with INDIRECT...

Also why are you not using A2 instead of INDIRECT(ADDRESS(ROW(), 1))? I
don't see any variable there other than row() which you any way get with 2 in
A2...




"JG" wrote:
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.