V Lookup - How do you make the "Col_index_number" updated

[Guest]

I have a data base set up in Excel that records all manor of information. I
then use "Vlookup" to extract information into different tabs of the
spreadsheet.

Ie) to one tab provides the summary of the address.

The problem that I have is that if we introduce another column to the data
base the "Col_index_number" on the Vlookup does not automatically update. Ie)
the information that you wanted to look up use to be in Column 6, but once
you inserted another cell it is now in column 7, but the lookup is still
refering to Column 6.

Is there a way around this.
Michael

 

[Ron Rosenfeld]

I have a data base set up in Excel that records all manor of information. I
then use "Vlookup" to extract information into different tabs of the
spreadsheet.

Ie) to one tab provides the summary of the address.

The problem that I have is that if we introduce another column to the data
base the "Col_index_number" on the Vlookup does not automatically update. Ie)
the information that you wanted to look up use to be in Column 6, but once
you inserted another cell it is now in column 7, but the lookup is still
refering to Column 6.

Is there a way around this.
Michael

If the columns of your DB have labels, then you could use the MATCH function to
compute the proper column number.


--ron

 

[Max]

Instead of say:

In Sheet2
-------------
In B1: =VLOOKUP($A1,Sheet1!$A:$C,2,0)
In C1: =VLOOKUP($A1,Sheet1!$A:$C,3,0)

(B1:C1 copied down)

Try something like:

In B1:

=VLOOKUP($A1,Sheet1!$A:$C,COLUMNS(Sheet1!$A:B),0)

Copy B1 across to C1, fill down

Now when new cols are inserted in the table_array in Sheet1's cols A to C,
the VLOOKUP formulas above in cols B and C will auto-adjust and maintain the
same returns