I thought there was a way you could indicate the column index number argument
by referring to the column name. Only the column index number seems to work.
If you have a particularly large lookup table with many columns, the column
index number may be inconvenient. Is there a different way you can define the
column index number other than the column index number?

Not sure what you mean by column "name". Do you mean "B", "C", "D" or do you
mean column headings (eg "Jan", "Feb", "Mar")?

Assuming the table is in B1:Z200, I sometimes use something like:
=VLOOKUP("Test", B1:Z200, COLUMNS(B:G), 0)
if I want to return whatever is in column G. Also, if I want to insert a
new column into the table before column G, it won't mess up my existing
lookup functions (unlike a hardcoded 6).

If you want to determine which column to return based on column headings,
use the match function:

=VLOOKUP("Test", B1:Z200, Match("Jun", B1:Z1, 0), 0)



"ibvalentine" wrote:

I thought there was a way you could indicate the column index number argument
by referring to the column name. Only the column index number seems to work.
If you have a particularly large lookup table with many columns, the column
index number may be inconvenient. Is there a different way you can define the
column index number other than the column index number?

I assume you're referring to copying the formula across columns, along a
row, and have the column index number increment, in order to return multiple
columns (fields) of data from a data base.

One way:

=VLOOKUP(A1, $B$1:$Z$100,COLUMNS($B$1:C1),0)

As you copy this type formula across, the column index number will
automatically increment

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"ibvalentine" wrote in message
...
I thought there was a way you could indicate the column index number
argument
by referring to the column name. Only the column index number seems to
work.
If you have a particularly large lookup table with many columns, the
column
index number may be inconvenient. Is there a different way you can define
the
column index number other than the column index number?

One more possible.

=VLOOKUP(A1,$B$10:$M$100,{2,3,4,5,6,7,8,9,10,11},F ALSE)

Select 10 contiguous cells in a row left to right. Type the formula in first of
these then hit CTRL + SHIFT + ENTER to increment the col-index number.


Gord Dibben MS Excel MVP


On Sun, 16 Sep 2007 17:00:00 -0700, ibvalentine
wrote:

I thought there was a way you could indicate the column index number argument
by referring to the column name. Only the column index number seems to work.
If you have a particularly large lookup table with many columns, the column
index number may be inconvenient. Is there a different way you can define the
column index number other than the column index number?

I appreciate all the responses but let me clarify my question. Let's say I
want to use vlookup to fill in data for an invoice. I have a customer table
in another worksheet I will use for the lookup table which is named
"customers". The column headings are CustomerID, Company Name, Owner,
Address, City, State, and Zip Code. So, if I want to fill in the company
name, my vlookup function on the invoice is:

vlookup(E3, customers, 2, false)

I am using "2" for the column index number. My question is can I use
something other than the column index number to refer to the column that
contains the value I am looking up? Most have suggested Columns(range of
columns). I just want to refer to that one specific column with the heading
Company Name. And this would have to work with several vlookup functions.

"Gord Dibben" wrote:

One more possible.

=VLOOKUP(A1,$B$10:$M$100,{2,3,4,5,6,7,8,9,10,11},F ALSE)

Select 10 contiguous cells in a row left to right. Type the formula in first of
these then hit CTRL + SHIFT + ENTER to increment the col-index number.


Gord Dibben MS Excel MVP


On Sun, 16 Sep 2007 17:00:00 -0700, ibvalentine
wrote:

I thought there was a way you could indicate the column index number argument
by referring to the column name. Only the column index number seems to work.
If you have a particularly large lookup table with many columns, the column
index number may be inconvenient. Is there a different way you can define the
column index number other than the column index number?

If I follow you, say your "customers" range was,
Sheet2!A1:H25
With headers in Row 1.

Try this, with you typing in the column header you want returned in E1 of
the sheet containing the formula:

=VLOOKUP(E3, customers, MATCH(E1,Sheet2!A1:H1,0), 0)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ibvalentine" wrote in message
...
I appreciate all the responses but let me clarify my question. Let's say I
want to use vlookup to fill in data for an invoice. I have a customer table
in another worksheet I will use for the lookup table which is named
"customers". The column headings are CustomerID, Company Name, Owner,
Address, City, State, and Zip Code. So, if I want to fill in the company
name, my vlookup function on the invoice is:

vlookup(E3, customers, 2, false)

I am using "2" for the column index number. My question is can I use
something other than the column index number to refer to the column that
contains the value I am looking up? Most have suggested Columns(range of
columns). I just want to refer to that one specific column with the heading
Company Name. And this would have to work with several vlookup functions.

"Gord Dibben" wrote:

One more possible.

=VLOOKUP(A1,$B$10:$M$100,{2,3,4,5,6,7,8,9,10,11},F ALSE)

Select 10 contiguous cells in a row left to right. Type the formula in
first of
these then hit CTRL + SHIFT + ENTER to increment the col-index number.


Gord Dibben MS Excel MVP


On Sun, 16 Sep 2007 17:00:00 -0700, ibvalentine
wrote:

I thought there was a way you could indicate the column index number
argument
by referring to the column name. Only the column index number seems to
work.
If you have a particularly large lookup table with many columns, the
column
index number may be inconvenient. Is there a different way you can define
the
column index number other than the column index number?

All the responses were helpful. Here is the solutions I came up with that
works:

=VLOOKUP(A4, customers, COLUMN(Accounts!B2:B7), FALSE)

I am sure there was a function that I did a few years ago, where you could
use the column index number or the name of the column heading, in this case
"Company Name". I thought it was a vlookup but maybe it was a database
function. Anyway, I got my answer thanks to everyone's help!

"RagDyeR" wrote:

If I follow you, say your "customers" range was,
Sheet2!A1:H25
With headers in Row 1.

Try this, with you typing in the column header you want returned in E1 of
the sheet containing the formula:

=VLOOKUP(E3, customers, MATCH(E1,Sheet2!A1:H1,0), 0)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ibvalentine" wrote in message
...
I appreciate all the responses but let me clarify my question. Let's say I
want to use vlookup to fill in data for an invoice. I have a customer table
in another worksheet I will use for the lookup table which is named
"customers". The column headings are CustomerID, Company Name, Owner,
Address, City, State, and Zip Code. So, if I want to fill in the company
name, my vlookup function on the invoice is:

vlookup(E3, customers, 2, false)

I am using "2" for the column index number. My question is can I use
something other than the column index number to refer to the column that
contains the value I am looking up? Most have suggested Columns(range of
columns). I just want to refer to that one specific column with the heading
Company Name. And this would have to work with several vlookup functions.

"Gord Dibben" wrote:

One more possible.

=VLOOKUP(A1,$B$10:$M$100,{2,3,4,5,6,7,8,9,10,11},F ALSE)

Select 10 contiguous cells in a row left to right. Type the formula in
first of
these then hit CTRL + SHIFT + ENTER to increment the col-index number.


Gord Dibben MS Excel MVP


On Sun, 16 Sep 2007 17:00:00 -0700, ibvalentine
wrote:

I thought there was a way you could indicate the column index number
argument
by referring to the column name. Only the column index number seems to
work.
If you have a particularly large lookup table with many columns, the
column
index number may be inconvenient. Is there a different way you can define
the
column index number other than the column index number?