vlookup column index number argument

I

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

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)
 
R

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
 
G

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
 
I

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

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

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!
 

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

Top