vlookup column index number argument

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ibvalentine - 17 Sep 2007 01:00 GMT

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?

Reply to this Message

JMB - 17 Sep 2007 01:28 GMT

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)

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

Reply to this Message

Ragdyer - 17 Sep 2007 01:28 GMT

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

Signature

HTH,

RD

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

Reply to this Message

Gord Dibben - 17 Sep 2007 03:39 GMT

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

Reply to this Message

ibvalentine - 17 Sep 2007 05:40 GMT

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.

> One more possible.
>
[quoted text clipped - 10 lines]
> >index number may be inconvenient. Is there a different way you can define the
> >column index number other than the column index number?

Reply to this Message

RagDyeR - 17 Sep 2007 15:26 GMT

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)

Signature

HTH,

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

> One more possible.
>
[quoted text clipped - 18 lines]
> >the
> >column index number other than the column index number?

Reply to this Message

ibvalentine - 17 Sep 2007 16:26 GMT

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!

> If I follow you, say your "customers" range was,
> Sheet2!A1:H25
[quoted text clipped - 44 lines]
> > >the
> > >column index number other than the column index number?

Reply to this Message