Lookup funtion: column index number (third parameter)
 

I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize column
headings as the "column index number" and allow you to pick these from a list
once the table name is selected.

The way to work around this is to include a row of numbers above the lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price") and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

Here's what I do: I insert a row at the top of the table (assume table goes
from column A to column Z) and enter 1 in A1. Then =A1+1 in B1 and fill to
the right as far as the table goes.

Ergo, when I find the relevant column for the column lookup, all I have to
do is reference the number in the first row of the spreadsheet. Seems far
easier than relying on Microsoft to make it easier.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"KipB" wrote:

I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize column
headings as the "column index number" and allow you to pick these from a list
once the table name is selected.

The way to work around this is to include a row of numbers above the lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price") and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

If your columns have descriptive headers then you can use the MATCH function
to find it for you:

.........A......B.......C.......D.......E
1.............Joe....Biff.....Sue....Tom
2...Mon...10......22......30......15
3...Tue......0.......40......4.......19
4...Wed....0.......72......6.......20

Lookup: Tue, Biff

A10 = Tue
B10 = Biff

=VLOOKUP(A10,A2:E4,MATCH(B10,A1:E1,0),0)

Biff

"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize
column
headings as the "column index number" and allow you to pick these from a
list
once the table name is selected.

The way to work around this is to include a row of numbers above the
lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price")
and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

That's very clever.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"T. Valko" wrote:

If your columns have descriptive headers then you can use the MATCH function
to find it for you:

.........A......B.......C.......D.......E
1.............Joe....Biff.....Sue....Tom
2...Mon...10......22......30......15
3...Tue......0.......40......4.......19
4...Wed....0.......72......6.......20

Lookup: Tue, Biff

A10 = Tue
B10 = Biff

=VLOOKUP(A10,A2:E4,MATCH(B10,A1:E1,0),0)

Biff

"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize
column
headings as the "column index number" and allow you to pick these from a
list
once the table name is selected.

The way to work around this is to include a row of numbers above the
lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price")
and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

Hi

Use Index / Match instead
If your table has labels in A2:A10 and labels in B1:G1

=IDEX($A$1:$G$10,MATCH(Row_Label,$A$1:$A$10,0),MAT CH(Column_label,$B$1:$G$1,0))

--
Regards

Roger Govier


"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the
"column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize
column
headings as the "column index number" and allow you to pick these from
a list
once the table name is selected.

The way to work around this is to include a row of numbers above the
lookup
table that counts the columns. This could be hard data or better yet
the
formula: =column(X)-column($A)+1. Using the formula updates the
column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the
desired
column by pointing, or better still give the reference a name
("Price") and
use the name as the reference. Now you have a meaningful lookup
function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

Instead of counting across...................

To return the column number from a letter use this Function

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("IV") returns 256


Gord Dibben MS Excel MVP


On Fri, 2 Feb 2007 17:46:28 -0500, "T. Valko" wrote:

If your columns have descriptive headers then you can use the MATCH function
to find it for you:

........A......B.......C.......D.......E
1.............Joe....Biff.....Sue....Tom
2...Mon...10......22......30......15
3...Tue......0.......40......4.......19
4...Wed....0.......72......6.......20

Lookup: Tue, Biff

A10 = Tue
B10 = Biff

=VLOOKUP(A10,A2:E4,MATCH(B10,A1:E1,0),0)

Biff

"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the "column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize
column
headings as the "column index number" and allow you to pick these from a
list
once the table name is selected.

The way to work around this is to include a row of numbers above the
lookup
table that counts the columns. This could be hard data or better yet the
formula: =column(X)-column($A)+1. Using the formula updates the column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the desired
column by pointing, or better still give the reference a name ("Price")
and
use the name as the reference. Now you have a meaningful lookup function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

On Feb 2, 3:23 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Instead of counting across...................

To return the column number from a letter use this Function

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("IV") returns 256
....

Someone's gotta ask . . . why is this preferred to just

=COLUMN(IV:IV)

?

Still, it's a pity Microsoft still hasn't figured out how to implement
a work-alike for 123's @XINDEX function, which given Biff's setup,
would return the desired result with the formula
@XINDEX(A2:E4,B10,A10) (note 123's column then row ordering of index
arguments).

Lookup funtion: column index number (third parameter)
 

Instead of using =vlookup or =hlookup, use =lookup. That way you don't have
to count columns

"Roger Govier" wrote:

Hi

Use Index / Match instead
If your table has labels in A2:A10 and labels in B1:G1

=IDEX($A$1:$G$10,MATCH(Row_Label,$A$1:$A$10,0),MAT CH(Column_label,$B$1:$G$1,0))

--
Regards

Roger Govier


"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the
"column
index number", the third parameter in the VLOOKUP funtion. It is very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would recognize
column
headings as the "column index number" and allow you to pick these from
a list
once the table name is selected.

The way to work around this is to include a row of numbers above the
lookup
table that counts the columns. This could be hard data or better yet
the
formula: =column(X)-column($A)+1. Using the formula updates the
column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the
desired
column by pointing, or better still give the reference a name
("Price") and
use the name as the reference. Now you have a meaningful lookup
function
such as: =vlookup(A6,Data,Price,false).

Lookup funtion: column index number (third parameter)
 

Hi Jason

Yes, you're correct that you don't have to specify column, but it will
only return the value from the last column of the array, and the array
would have to be sorted.

It wouldn't work in this particular scenario.

--
Regards

Roger Govier


"jasonc" wrote in message
...
Instead of using =vlookup or =hlookup, use =lookup. That way you
don't have
to count columns

"Roger Govier" wrote:

Hi

Use Index / Match instead
If your table has labels in A2:A10 and labels in B1:G1

=IDEX($A$1:$G$10,MATCH(Row_Label,$A$1:$A$10,0),MAT CH(Column_label,$B$1:$G$1,0))

--
Regards

Roger Govier


"KipB" wrote in message
...
I was hoping Excel 2007 would have a simpler way of identifying the
"column
index number", the third parameter in the VLOOKUP funtion. It is
very
tedious to count columns (especially when they are listed
alphabetically) -
and the task is even more difficult when dealing with large tables
or
references that don't begin in column "A".

TIP for MSExcel 2007 - Ideally the named lookup table would
recognize
column
headings as the "column index number" and allow you to pick these
from
a list
once the table name is selected.

The way to work around this is to include a row of numbers above
the
lookup
table that counts the columns. This could be hard data or better
yet
the
formula: =column(X)-column($A)+1. Using the formula updates the
column
number if the lookup table adds or deletes columns. In the lookup
function
for "column index number" you can enter the cell reference for the
desired
column by pointing, or better still give the reference a name
("Price") and
use the name as the reference. Now you have a meaningful lookup
function
such as: =vlookup(A6,Data,Price,false).