vlookup with different col_index_num

[ArcticWolf]

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

 

[Pete_UK]

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$1,"heading",0)

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

Hope this helps.

Pete

 

[Pete_UK]

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

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

Hope this helps.

Pete

 

[TGV]

I think u can go for Dsum. For more details about DSUM see help.

 

[TGV]

By mistake i have mentioned DSUM it's not DSUM it's DGET.

 

[Jacob Skaria]

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

 

[ArcticWolf]

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]

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