Table Lookup (VLookup, Index, Match)
 

intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

--
Regards,
Tom Ogilvy


"Budget Programmer" wrote in
message ...
I have a Table Lookup problem, but I can't seem to get it to work. Could
it
be
because I'm on Excel 2000?

My table is something like
Jan Feb Mar
Jones 1 2 3
Smith 4 5 6
Murphy 7 8 9
My statement is:
intMonthlyInitiativeHours = Index(A1:D4, Match("Smith", A1:D4, 0),
Match("Feb", A1:D4, 0))
I get
Compile Error: Expected: list seperator or )
and it points to the first colon.
Can you please help? Many Thanks.

--
Programmer on Budget

Table Lookup (VLookup, Index, Match)
 

Tom Ogilvy wrote:
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

Small typo: "A1:D4" should be "A1:D1".

If names are created out of the row and column headers,

intMonthlyInitiativeHours = [Smith Feb]

Alan Beban

Table Lookup (VLookup, Index, Match)
 

Hi Tom,

Havent used match,index etc in VB... But

In the column argument for match you have indicated
"Application.Match("Feb", Range("A1:D4"), 0))" where the range is not a 1D
array?

Shouldnt it be "Application.Match("Feb", Range("A1:D1"), 0))"

Please correct me if am wrong.

Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

--
Regards,
Tom Ogilvy


"Budget Programmer" wrote in
message ...
I have a Table Lookup problem, but I can't seem to get it to work.
Could
it
be
because I'm on Excel 2000?

My table is something like
Jan Feb Mar
Jones 1 2 3
Smith 4 5 6
Murphy 7 8 9
My statement is:
intMonthlyInitiativeHours = Index(A1:D4, Match("Smith", A1:D4, 0),
Match("Feb", A1:D4, 0))
I get
Compile Error: Expected: list seperator or )
and it points to the first colon.
Can you please help? Many Thanks.

--
Programmer on Budget

Table Lookup (VLookup, Index, Match)
 

Yes, that was a typo in that I didn't thoroughly clean up what the OP
presented. Thanks for the correction.

--
Regards,
Tom Ogilvy


"Hari" wrote in message
...
Hi Tom,

Havent used match,index etc in VB... But

In the column argument for match you have indicated
"Application.Match("Feb", Range("A1:D4"), 0))" where the range is not a 1D
array?

Shouldnt it be "Application.Match("Feb", Range("A1:D1"), 0))"

Please correct me if am wrong.

Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

--
Regards,
Tom Ogilvy


"Budget Programmer" wrote
in
message ...
I have a Table Lookup problem, but I can't seem to get it to work.
Could
it
be
because I'm on Excel 2000?

My table is something like
Jan Feb Mar
Jones 1 2 3
Smith 4 5 6
Murphy 7 8 9
My statement is:
intMonthlyInitiativeHours = Index(A1:D4, Match("Smith", A1:D4, 0),
Match("Feb", A1:D4, 0))
I get
Compile Error: Expected: list seperator or )
and it points to the first colon.
Can you please help? Many Thanks.

--
Programmer on Budget

Table Lookup (VLookup, Index, Match)
 

Thanks for the correction.

--
Regards,
Tom Ogilvy

"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

Small typo: "A1:D4" should be "A1:D1".

If names are created out of the row and column headers,

intMonthlyInitiativeHours = [Smith Feb]

Alan Beban

Table Lookup (VLookup, Index, Match)
 

Many Thanks Everyone. That did the trick.

"Tom Ogilvy" wrote:

Yes, that was a typo in that I didn't thoroughly clean up what the OP
presented. Thanks for the correction.

--
Regards,
Tom Ogilvy


"Hari" wrote in message
...
Hi Tom,

Havent used match,index etc in VB... But

In the column argument for match you have indicated
"Application.Match("Feb", Range("A1:D4"), 0))" where the range is not a 1D
array?

Shouldnt it be "Application.Match("Feb", Range("A1:D1"), 0))"

Please correct me if am wrong.

Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

--
Regards,
Tom Ogilvy


"Budget Programmer" wrote
in
message ...
I have a Table Lookup problem, but I can't seem to get it to work.
Could
it
be
because I'm on Excel 2000?

My table is something like
Jan Feb Mar
Jones 1 2 3
Smith 4 5 6
Murphy 7 8 9
My statement is:
intMonthlyInitiativeHours = Index(A1:D4, Match("Smith", A1:D4, 0),
Match("Feb", A1:D4, 0))
I get
Compile Error: Expected: list seperator or )
and it points to the first colon.
Can you please help? Many Thanks.

--
Programmer on Budget

Table Lookup (VLookup, Index, Match)
 

Hi Alan,

Just wanted to understand what is this method of intMonthlyInitiativeHours =
[Smith Feb]

I did designate row 3 as smith and column C as Feb and it worked.

I want to understand the syntax of the statement "= [Smith Feb]". How is
this interpreted as. Is it some array or what?

Regards,
Hari
India

"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

Small typo: "A1:D4" should be "A1:D1".

If names are created out of the row and column headers,

intMonthlyInitiativeHours = [Smith Feb]

Alan Beban

Table Lookup (VLookup, Index, Match)
 

a space in a formula is the intersection operator

you can do

=A:A 3:3

to refer to A3.

putting brackets around it and using it in Excel vba is like creating a
virtual cell.

? [A:A 3:3].Address
$A$3
? [A:A 3:3]
AA Auto Parts

So if you have defined names (insert = name = define) that refer to a row
range and a column range, you can do what Alan suggest, substituting the
range names for the hard coded addresses I have shown.

A lot of people use this (square brackets) as a shortcut for referring to a
name. Instead of Range("A1") they will use [A1]. However, this is 3 to 4
times slower or worse since you have to use Excel to evaluate the value.

likewise, I would think

intMonthlyInitiativeHours = [Smith Feb]


would be slower than
intMonthlyInitiativeHours = Intersect(Range("Smith"), Range("Feb")).Value


--
Regards,
Tom Ogilvy



"Hari" wrote in message
...
Hi Alan,

Just wanted to understand what is this method of intMonthlyInitiativeHours
=
[Smith Feb]

I did designate row 3 as smith and column C as Feb and it worked.

I want to understand the syntax of the statement "= [Smith Feb]". How is
this interpreted as. Is it some array or what?

Regards,
Hari
India

"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

Small typo: "A1:D4" should be "A1:D1".

If names are created out of the row and column headers,

intMonthlyInitiativeHours = [Smith Feb]

Alan Beban

Table Lookup (VLookup, Index, Match)
 

Hi Tom,

Thanx a lot for offering some interesting insights on this. Very grateful to
you.

Regards,
Hari
India

"Tom Ogilvy" wrote in message
...
a space in a formula is the intersection operator

you can do

=A:A 3:3

to refer to A3.

putting brackets around it and using it in Excel vba is like creating a
virtual cell.

? [A:A 3:3].Address
$A$3
? [A:A 3:3]
AA Auto Parts

So if you have defined names (insert = name = define) that refer to a
row
range and a column range, you can do what Alan suggest, substituting the
range names for the hard coded addresses I have shown.

A lot of people use this (square brackets) as a shortcut for referring to
a
name. Instead of Range("A1") they will use [A1]. However, this is 3 to 4
times slower or worse since you have to use Excel to evaluate the value.

likewise, I would think

intMonthlyInitiativeHours = [Smith Feb]


would be slower than
intMonthlyInitiativeHours = Intersect(Range("Smith"), Range("Feb")).Value


--
Regards,
Tom Ogilvy



"Hari" wrote in message
...
Hi Alan,

Just wanted to understand what is this method of
intMonthlyInitiativeHours
=
[Smith Feb]

I did designate row 3 as smith and column C as Feb and it worked.

I want to understand the syntax of the statement "= [Smith Feb]". How is
this interpreted as. Is it some array or what?

Regards,
Hari
India

"Alan Beban" wrote in message
...
Tom Ogilvy wrote:
intMonthlyInitiativeHours = Application.Index(Range("A1:D4"), _
Application.Match("Smith", Range("A1:A4"), 0), _
Application.Match("Feb", Range("A1:D4"), 0))

Small typo: "A1:D4" should be "A1:D1".

If names are created out of the row and column headers,

intMonthlyInitiativeHours = [Smith Feb]

Alan Beban

Table Lookup (VLookup, Index, Match)
 

Tom Ogilvy wrote:

. . .
likewise, I would think

intMonthlyInitiativeHours = [Smith Feb]


would be slower than
intMonthlyInitiativeHours = Intersect(Range("Smith"), Range("Feb")).Value



In any event, although I'm not sure the difference is significant to
most users, my little test suggests that x = [Smith Feb] is in fact
about 25% faster than x = Intersect(Range("Smith"), Range("Feb")).Value;
about 62 microseconds compared to about 83 microseconds.

I don't find this surprising in light of the fact that x = [Smith Feb]
is equivalent to x = Evaluate("Smith Feb"), and I don't know enough to
know why one would think that Evaluate would be slower than Intersect.

I suppose different machines give different results; and I suppose my
testing methodology could be faulty. Perhaps Tom Ogilvy will test and
report back.

Alan Beban

Table Lookup (VLookup, Index, Match)
 

Tom Ogilvy wrote:

a space in a formula is the intersection operator

you can do

=A:A 3:3

to refer to A3.

putting brackets around it and using it in Excel vba is like creating a
virtual cell.

? [A:A 3:3].Address
$A$3
? [A:A 3:3]
AA Auto Parts

So if you have defined names (insert = name = define) that refer to a row
range and a column range, you can do what Alan suggest, substituting the
range names for the hard coded addresses I have shown.
In fact, using the Intersection Operator is a standard way, on the
worksheet, for referring to the values in a table. Rather than
Insert|Name|Define for a single row and column, one highlights the table
(including row and column headings) and uses Insert|Name|Create, and
checks Top row and Left Column; this then names all the rows and columns
of the table. The Row and Column headings have to be such as to qualify
as range names--e.g., names can't begin with numbers, can't be
combinations that would qualify as range addresses (A1, C3, etc.), etc.

Alan Beban

Table Lookup (VLookup, Index, Match)
 

I got similar results.

My stated impression that it would be slower is because using evaluate or
the equivalent [ ] would require Excel to evalute the argument. I would
expect this to incur overhead as it appears to do for a single range
reference. However, that doesn't seem to be the case - at least
comparative to VBA. Perhaps the VBA intersect function is inefficient.

--
Regards,
Tom Ogilvy



"Alan Beban" wrote in message
...
Tom Ogilvy wrote:

. . .
likewise, I would think

intMonthlyInitiativeHours = [Smith Feb]


would be slower than
intMonthlyInitiativeHours = Intersect(Range("Smith"),
Range("Feb")).Value



In any event, although I'm not sure the difference is significant to
most users, my little test suggests that x = [Smith Feb] is in fact
about 25% faster than x = Intersect(Range("Smith"), Range("Feb")).Value;
about 62 microseconds compared to about 83 microseconds.

I don't find this surprising in light of the fact that x = [Smith Feb]
is equivalent to x = Evaluate("Smith Feb"), and I don't know enough to
know why one would think that Evaluate would be slower than Intersect.

I suppose different machines give different results; and I suppose my
testing methodology could be faulty. Perhaps Tom Ogilvy will test and
report back.

Alan Beban