vlookup with different col_index_num

A

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
 
P

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

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

Hope this helps.

Pete
 
P

Pete_UK

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

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

Hope this helps.

Pete
 
J

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
 
A

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
 
P

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
 

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