VLookup - Column name?
 

I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though because
they represent a different company and thus a different column from the other
sheet (the one I'm getting data from). Is there anyway to instead of using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change (and I
have no control over this). However, the names remain the same.

VLookup - Column name?
 

A bit more detail

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though because
they represent a different company and thus a different column from the
other
sheet (the one I'm getting data from). Is there anyway to instead of
using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change (and I
have no control over this). However, the names remain the same.

VLookup - Column name?
 

Worksheet 1:

Company 1
Allocations Jan Feb Mar
Ports 2 3 2
Install 187 181 190

The March formula looks like:
=IF(ISNA(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)) ,0,(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)))

So column 38 in the ppweb worksheet represents Company 1. However next
month there might have been a column added that now makes Company 1 in column
39.

So instead of using numbers as column identifiers is there a way to lookup
the name of the column?

"Don Guillett" wrote:

A bit more detail

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though because
they represent a different company and thus a different column from the
other
sheet (the one I'm getting data from). Is there anyway to instead of
using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change (and I
have no control over this). However, the names remain the same.

VLookup - Column name?
 

try this idea
insertnamedefinecol38in the formula box type in
=column(al$1)
=vlookup(a24,yourtable,col38,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
Worksheet 1:

Company 1
Allocations Jan Feb Mar
Ports 2 3 2
Install 187 181 190

The March formula looks like:
=IF(ISNA(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)) ,0,(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)))

So column 38 in the ppweb worksheet represents Company 1. However next
month there might have been a column added that now makes Company 1 in
column
39.

So instead of using numbers as column identifiers is there a way to lookup
the name of the column?

"Don Guillett" wrote:

A bit more detail

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though
because
they represent a different company and thus a different column from the
other
sheet (the one I'm getting data from). Is there anyway to instead of
using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change (and
I
have no control over this). However, the names remain the same.

VLookup - Column name?
 

Which worksheet do I do this in? And what does the Al$1 represent?

I should have also described the worksheet that I'm looking up data in.

ppweb: March
Company Co1 Co2 Co3
ports 2 5 0
Install 190 50 0

So I'd like to find a way to return the column number for the VLookup
formula by somehow matching the company names. (I don't mind typing into the
formula on each sheet which company it needs to match)

Like is there a way for me to tell it to find Co1 in ppweb worksheet in row
1 and return the colmn number?

"Don Guillett" wrote:

try this idea
insertnamedefinecol38in the formula box type in
=column(al$1)
=vlookup(a24,yourtable,col38,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
Worksheet 1:

Company 1
Allocations Jan Feb Mar
Ports 2 3 2
Install 187 181 190

The March formula looks like:
=IF(ISNA(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)) ,0,(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)))

So column 38 in the ppweb worksheet represents Company 1. However next
month there might have been a column added that now makes Company 1 in
column
39.

So instead of using numbers as column identifiers is there a way to lookup
the name of the column?

"Don Guillett" wrote:

A bit more detail

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though
because
they represent a different company and thus a different column from the
other
sheet (the one I'm getting data from). Is there anyway to instead of
using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change (and
I
have no control over this). However, the names remain the same.

VLookup - Column name?
 

Then you don't need the defined name unless you desire to use match to find
the column heading in row 1
=VLOOKUP(a24,$A$1:$x$21,MATCH("co1",1:1),0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
Which worksheet do I do this in? And what does the Al$1 represent?

I should have also described the worksheet that I'm looking up data in.

ppweb: March
Company Co1 Co2 Co3
ports 2 5 0
Install 190 50 0

So I'd like to find a way to return the column number for the VLookup
formula by somehow matching the company names. (I don't mind typing into
the
formula on each sheet which company it needs to match)

Like is there a way for me to tell it to find Co1 in ppweb worksheet in
row
1 and return the colmn number?

"Don Guillett" wrote:

try this idea
insertnamedefinecol38in the formula box type in
=column(al$1)
=vlookup(a24,yourtable,col38,0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
Worksheet 1:

Company 1
Allocations Jan Feb Mar
Ports 2 3 2
Install 187 181 190

The March formula looks like:
=IF(ISNA(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)) ,0,(VLOOKUP($A24,ppweb!$B$2:$BZ$13,38,FALSE)))

So column 38 in the ppweb worksheet represents Company 1. However next
month there might have been a column added that now makes Company 1 in
column
39.

So instead of using numbers as column identifiers is there a way to
lookup
the name of the column?

"Don Guillett" wrote:

A bit more detail

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jennifer" wrote in message
...
I currently use VLookup on a few worksheets to get data from another
worksheet. I don't use the same formula for each worksheet though
because
they represent a different company and thus a different column from
the
other
sheet (the one I'm getting data from). Is there anyway to instead
of
using a
number for the column VLookup is searching in to use a name?

I ask this because the column numbers will more than likely change
(and
I
have no control over this). However, the names remain the same.