Vlookup Problem

[rastus]

Hi

I have a standard vlookup formula as follows:

=VLOOKUP(A6,A14:D100,2,FALSE)

where the function looks up the value in Column A, and returns the corresponding value in Column B ('2').

The problem is that the data in my spreadsheet is copied from another software program where columns are sometimes added or deleted, so I have to change the column number in the formula whenever this happens.

Is it possible for the vlookup formula to be based on the column header rather than the column number?

Thanks for any help you can give.

R

 

[acw]

Hi

along the lines of

=vlookup(a6,a14:d100,match("heading required","heading range",0),false)

Tony

 

[Barrie Davidson]

Where is the column header found? Is it always in row 14? What value in the header row are you looking for to identify
a) the value being looked up (column A in your example)?
b) the value to return (column B in your example)?

Regards,

 

[rastus]

Thanks Tony! I'll try that right now.

 

[Ron Morris]

You can try the following:

=VLOOKUP(A6,$A:$D,MATCH(Column Header,$A$1:$D$1,FALSE)

Please note: change your range if the column count will fluctuate.

Also, there may be issues if the columns are not in alphabetical order from left to right.

Ron

 

[rastus]

Hi Barry

Each column has a header name. Column 2 is called "Approved".
But the column holding the value to turn (Column 2) will not always be in Column 2. Sometimes it will be Column 3 or 4 if new columns are added between it and Column 1. So rather than change the column number, I would like the formula to always find the header named "Approved" regardless of which column number it is. Does this make sense?

 

[Barrie Davidson]

Hi Barry

Each column has a header name. Column 2 is called "Approved".
But the column holding the value to turn (Column 2) will not always be in Column 2. Sometimes it will be Column 3 or 4 if new columns are added between it and Column 1. So rather than change the column number, I would like the formula to always find the header named "Approved" regardless of which column number it is. Does this make sense?

Rastus, what row is the header on? Row 14? Is it possible that your data would extend beyond column D? If yes to all the above, tweak Tony's formula to read:

=VLOOKUP(A6,A14:Z100,MATCH("Approved",14:14,0),0)

Are we getting close yet?

 

[rastus]

Oh, I understand. Yes - the headers will always be in row 14.

Thanks Barry, Tony & Ron. I'll give your example a try.