vlookup - inserting columns in array

S

Steverh

I have an excel XP workbook with multiple worksheets where I need t
lookup values in one spreadsheet and put the values in a cell i
another worksheet.

I have many columns that I need to lookup within my lookup range an
certain of the columns are entered in alpha order. However, if I wan
to insert new columns all of the existing column references in m
lookup formula are now incorrect if they appear to the right of th
inserted columns. Is there a way of refering to the coulmns relativel
(e.g. 4 cols the the right of col A, which then get renumbered as ne
columns are inserted to the left of them) instead of using the colum
no.
 
D

Dave Peterson

So you want to pick out the correct column by matching on the header--and same
thing for the correct row?

Here's a formula stolen from Peo Sjoblom (and others):

=INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX(Table,1,),0))

If you create a range name referring to your cross reference table
(Insert|Name|Define) and call it Table, it'll make your typing life a little
easier. (But you could use the address (Sheet2!$a$1:$x$9999) in place of Table
in the formula.

And A1 will hold the value to match on the column (to get the right row) and B1
will be used to get the correct column.
 
R

Robert Christie

Hi Dave
New to Vlookup formulas.
I can't figure out what your formula does.
What is in B1 that tells it to return a value from a
specific column.

TIA
Bob C.
-----Original Message-----
So you want to pick out the correct column by matching on the header--and same
thing for the correct row?

Here's a formula stolen from Peo Sjoblom (and others):

=INDEX(Table,MATCH(A1,INDEX(Table,,1),0),MATCH(B1,INDEX (Table,1,),0))

If you create a range name referring to your cross reference table
(Insert|Name|Define) and call it Table, it'll make your typing life a little
easier. (But you could use the address (Sheet2!
Click to expand...
$a$1:$x$9999) in place of Table
 
D

Dave Peterson

You have to give it the header to match up.

If both the lookup table and the area that you're populating have the same
headers (row and columns), you can use them. Those headers don't have to be in
the same order, and you don't need all of them--just the ones to match up on.

In my little example, A1 held the value to match up in the first column of that
Table.
B1 held the value to match in the first row of the column.

Maybe this would be better:

=INDEX(table,MATCH($A2,INDEX(table,,1),0),MATCH(B$1,INDEX(table,1,),0))

(I put this in B2.)

Where column A and Row 1 held the headers.

Try that once and then just temporarily change the header in B1 to a different
column header from the table.
 
D

Dave Peterson

And sorry, that really original response wasn't much help if you've never used
index(match()) before.
 
R

Robert Christie

Hi Dave
Thank you for your reply, I need to read up on them and
try them out.

Regards Bob C.
 
Top