vlookup change column index position - without changing formulae
 

I have a monthly report from a data warehouse which will have addditional
suppliers periodically reported alphabetically - thereby changing the col
index no of some customers.

For Example July I have 4 customers A, C, D and G so my work sheet is
referring to say columns 1, 2, 3 and 4 in the lookup.

I gain a customer B in August so in my lookup cust C has moved to col 3, D
to col 4, and G to column 5 in the lookup.

I still need the worksheet to pull the correct data for the relevant
customer without rewriting the formulae each time there are changes - it's
too manual and human error risks are high!

To put this in perspective one of the reports I have vlookup formulae in
runs from column A to column IJ - lots of formulae to move if i get a new
customer whose name begins with an A!

Any suggestions greatly appreciated!

Use a cell value. Say the cells are in row 1, then store the column id in
A10, and use a lookup such as =VLOOKUP(INDIRECT(A10&"1"),M1:P8,2,FALSE)

Then all you do is plug the column id in A10.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"loopyloobyloo" wrote in message
...
I have a monthly report from a data warehouse which will have addditional
suppliers periodically reported alphabetically - thereby changing the col
index no of some customers.

For Example July I have 4 customers A, C, D and G so my work sheet is
referring to say columns 1, 2, 3 and 4 in the lookup.

I gain a customer B in August so in my lookup cust C has moved to col 3, D
to col 4, and G to column 5 in the lookup.

I still need the worksheet to pull the correct data for the relevant
customer without rewriting the formulae each time there are changes - it's
too manual and human error risks are high!

To put this in perspective one of the reports I have vlookup formulae in
runs from column A to column IJ - lots of formulae to move if i get a new
customer whose name begins with an A!

Any suggestions greatly appreciated!