vlookup with different col_index_num
 

Hi,

I have a vlookup which returns the value from a table called data. The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...)

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

You could use MATCH on the column headings to return the column
number, so instead of your 3 you would have something like:

MATCH(A$1:D$1,"heading",0)

If columns are inserted between A and D the formula will automatically
adjust.

Hope this helps.

Pete

On Aug 17, 9:17*am, ArcticWolf
wrote:
Hi,

I have a vlookup which returns the value from a table called data. *The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. *This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) *

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

Sorry, I got that the wrong way round. Try it this way:

MATCH("heading",A$1:D$1,0)

Hope this helps.

Pete

On Aug 17, 9:34*am, Pete_UK wrote:
You could use MATCH on the column headings to return the column
number, so instead of your 3 you would have something like:

MATCH(A$1:D$1,"heading",0)

If columns are inserted between A and D the formula will automatically
adjust.

Hope this helps.

Pete

On Aug 17, 9:17*am, ArcticWolf
wrote:



Hi,

I have a vlookup which returns the value from a table called data. *The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. *This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) *

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW- Hide quoted text -

- Show quoted text -

vlookup with different col_index_num
 

I think u can go for Dsum. For more details about DSUM see help.
--
If this post helps, pls click Yes
---------------
TGV


"ArcticWolf" wrote:

Hi,

I have a vlookup which returns the value from a table called data. The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...)

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

By mistake i have mentioned DSUM it's not DSUM it's DGET.
--
If this post helps, pls click Yes
---------------
TGV


"ArcticWolf" wrote:

Hi,

I have a vlookup which returns the value from a table called data. The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...)

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

Both the below formulas return the same;

Col A Col B Col C Col D
Rank Name Office
1 Tom R Bath =VLOOKUP(3,A:C,2,0)
2 Katy C Bath =VLOOKUP(3,A:C,MATCH("Name",1:1,0),0)
3 Nigel G Bath
4 Pete R Bath
5 Tony A London
6 John B London
7 Mary C London
8 Jane D London


If this post helps click Yes
---------------
Jacob Skaria


"ArcticWolf" wrote:

Hi,

I have a vlookup which returns the value from a table called data. The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...)

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

Thanks for the quick response Pete. Can I be a pain and ask you to amend my
original vlookup as I'm having difficulty placing the MATCH function into it
without getting an error. I'm trying to replace the "3" with the match
function, is this correct?

Thanks,

AW

"Pete_UK" wrote:

You could use MATCH on the column headings to return the column
number, so instead of your 3 you would have something like:

MATCH(A$1:D$1,"heading",0)

If columns are inserted between A and D the formula will automatically
adjust.

Hope this helps.

Pete

On Aug 17, 9:17 am, ArcticWolf
wrote:
Hi,

I have a vlookup which returns the value from a table called data. The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...)

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW

vlookup with different col_index_num
 

Yes, have a look at Jacob's solution.

I can amend your formula if you post it here, and if you can give me
details of the cell references used for your table.

Pete

On Aug 17, 10:52*am, ArcticWolf
wrote:
Thanks for the quick response Pete. *Can I be a pain and ask you to amend my
original vlookup as I'm having difficulty placing the MATCH function into it
without getting an error. *I'm trying to replace the "3" with the match
function, is this correct?

Thanks,

AW



"Pete_UK" wrote:
You could use MATCH on the column headings to return the column
number, so instead of your 3 you would have something like:

MATCH(A$1:D$1,"heading",0)

If columns are inserted between A and D the formula will automatically
adjust.

Hope this helps.

Pete

On Aug 17, 9:17 am, ArcticWolf
wrote:
Hi,

I have a vlookup which returns the value from a table called data. *The
column I want from the table is column 3, so =vlookup(a1,data,3,false)

However, the table will expand over the coming months with new columns
inserted before the 3rd column in the data table. *This will make my lookup
incorrect as the data I want is not in column 3 (but in 4 or 5 etc...) *

The column heading will always remain the same (if that is of use?), so how
can I get Excel to find the column I need as opposed to using the
col_index_num?

TIA,

AW- Hide quoted text -

- Show quoted text -