INDIRECT(ADDRESS) inside a VLOOKUP

J

JG

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.
 
T

Teethless mama

Too much to read...

JG said:
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.
Click to expand...
 
S

Sheeloo

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...
 
J

JG

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.
 
S

Sheeloo

Ok...

However won't it be easier to use something like "A"&i... not sure of the
syntax...

It might better if you have a large number of records...
 
J

JG

Possibly... But this is easy enough to extrapolate from for my other columns.

Thanks again. :)
 
J

JG

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

....only to end up with #REF. Is this only going to work within the same file?

Thanks.
 
D

Dave Peterson

First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

Sheeloo said:
Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)
Click to expand...
Click to expand...
 
J

JG

Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.
Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.

I have this formula in Work.xls which uses indirect.ext() by itself, and it
is pointing to a single cell reference:
=INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!$A$2")

This returns 1049, and I see that value whether Target File.xls is open or
closed.


In this formula, indirect.ext() is inside a vlookup:

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!$A$2:" &
ADDRESS((INDIRECT.EXT("'\\server\share\folder\[Target
File.xls]Count_TC'!$A$2")+1), 7)), 7)

I get a value returned just fine when Target File.xls is open, but not when
it's closed. Can I not use indirect.ext() to get data from a closed
spreadsheet to drive a vlookup()?

Oh, and =indirect("rc1",false) was cool, thanks for that.

Dave Peterson said:
First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

Sheeloo said:
Try
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"MainQueryResult!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I think you're seeing that the formula hasn't reevaluated. With the sending
workbook closed, select that cell with the =indirect() formula and hit F2
followed by enter.

What happens?

===========
I don't use =indirect.ext(), but maybe...

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" &
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7)

(Untested.)

(I hardcoded the G into the formula instead of using =address(..., 7). And I
dropped the $ signs from the strings (since it's in a string, it won't matter).

One suggestion...

If the used range of that sending worksheet isn't too much (you'll have to test
it), maybe you can drop the count_tc stuff and just use the entire column.

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7)



Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.
Thanks for pointing me to indirect.ext().

The help is less than helpful and I was hoping maybe you could explain why
in some cases, the function will pull a value with target spreadsheet open or
closed...and in other cases, only when it's open.

I have this formula in Work.xls which uses indirect.ext() by itself, and it
is pointing to a single cell reference:
=INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!$A$2")

This returns 1049, and I see that value whether Target File.xls is open or
closed.

In this formula, indirect.ext() is inside a vlookup:

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!$A$2:" &
ADDRESS((INDIRECT.EXT("'\\server\share\folder\[Target
File.xls]Count_TC'!$A$2")+1), 7)), 7)

I get a value returned just fine when Target File.xls is open, but not when
it's closed. Can I not use indirect.ext() to get data from a closed
spreadsheet to drive a vlookup()?

Oh, and =indirect("rc1",false) was cool, thanks for that.

Dave Peterson said:
First,
=INDIRECT(ADDRESS(ROW(), 1))
can be replaced with:
=indirect("rc1",false)
(same row, column 1)

But =indirect() won't work if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

As I've mentioned, this worked great...

I've got a similar issue where "MainQueryResult" is ina different file.
I've tried the following:

=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),INDIRECT(
"[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)

...only to end up with #REF. Is this only going to work within the same file?

Thanks.

:

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

JG

F2/Enter didn't work, neither did recalc....

To your formula ideas below, both of those options worked. With the file
open. But neither worked when the target was closed.
 
D

Dave Peterson

Sorry, I don't use =indirect.ext() enough to help debug your problem.

Maybe someone else will jump in.
F2/Enter didn't work, neither did recalc....

To your formula ideas below, both of those options worked. With the file
open. But neither worked when the target was closed.

Dave Peterson said:
I think you're seeing that the formula hasn't reevaluated. With the sending
workbook closed, select that cell with the =indirect() formula and hit F2
followed by enter.

What happens?

===========
I don't use =indirect.ext(), but maybe...

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT(("'\\server\share\folder\[Target File.xls]TCMetadata'!A2:G" &
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]Count_TC'!A2")), 7)

(Untested.)

(I hardcoded the G into the formula instead of using =address(..., 7). And I
dropped the $ signs from the strings (since it's in a string, it won't matter).

One suggestion...

If the used range of that sending worksheet isn't too much (you'll have to test
it), maybe you can drop the count_tc stuff and just use the entire column.

=VLOOKUP(INDIRECT("RC1", FALSE),
INDIRECT.EXT("'\\server\share\folder\[Target File.xls]TCMetadata'!A:G"), 7)

Dave Peterson
Click to expand...
Click to expand...
 
H

Harlan Grove

JG said:
=VLOOKUP(INDIRECT(ADDRESS(ROW(), 1)),
INDIRECT("[Filename.xls]'MainQueryResult'!$B$2:"&ADDRESS((Count_Comp!$A$2 + 1), 3)),2)
Click to expand...
....

Note: the external reference above needs to start off as either

'[Filename.xls]MainQueryResult'!$B$2:

or

[Filename.xls]MainQueryResult!$B$2:

but not as you show it above. IOW, the single quotes need to enclose
the filename portion of the reference as well as the worksheet name,
or you need to dispense with them. Wrapping only the worksheet names
in single quotes is a reference syntax error, so Excel would CORRECTLY
return #REF!.

This isn't the best way to handle a problem like this. There's no good
reason to use the first INDIRECT call. If you enter this formula in
cell X99, you'd be better off replacing the first INDIRECT call with
$A99. If you need to protect against range insertion/deletion moving
cells in column A, replace the first INDIRECT call with INDEX($A:$A,SUM
(ROW())). [SUM(ROW()) is intentional and protects against very subtle
bugs that can arise because ROW always returns an array, even single
results as single value arrays, and that can cause problems. Wrapping
ROW() inside SUM converts the result to a scalar value.]

Since you're using a sorted lookup, you could check whether your
lookup value from col A would be found by the Count_Comp!A2 row of the
external table, and only if so perform a lookup. Otherwise, just
return the value at the Count_Comp!A2 row.

=INDEX('[Filename.xls]MainQueryResult'!$B$2:$D$65536,
IF(INDEX($A:$A,SUM(ROW()))<INDEX('[Filename.xls]MainQueryResult'!$B
$2:$B$65536,Count_Comp!$A$2),
MATCH(INDEX($A:$A,SUM(ROW())),'[Filename.xls]MainQueryResult'!$B$2:$B
$65536),Count_Comp!$A$2))

This should recalc more quickly. It also avoids volatile functions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a sequence number(question number) to a paragraph which has a question and 4 options and answer 0
Need some PWA help 0
trouble with (complicated) vlookup searching another workbook 2
Help With VLOOKUP 2
Vlookup and return sheet name also 2
Vlookup 1
Finding dynamic maxima 9
help w indirect and index,match 3

Top