vlookup column index number argument

[ibvalentine]

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?

 

[JMB]

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)

 

[Ragdyer]

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

 

[Gord Dibben]

One more possible.

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

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

 

[ibvalentine]

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.

 

[RagDyeR]

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!
=====================================================

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.

 

[ibvalentine]

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!