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

[Michael]

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]

On Tue, 8 Feb 2005 19:37:06 -0800, "Michael"
<[email protected]> wrote:

>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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Michael" <[email protected]> wrote in message
news:[email protected]...
> 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