Vlookup Problem

rastus

Board Regular
Joined
Jul 8, 2005
Messages
157
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

along the lines of

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

Tony
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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,
 

Ron Morris

Active Member
Joined
Oct 18, 2004
Messages
430

ADVERTISEMENT

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

Board Regular
Joined
Jul 8, 2005
Messages
157
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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? :biggrin:
 

rastus

Board Regular
Joined
Jul 8, 2005
Messages
157
Oh, I understand. Yes - the headers will always be in row 14.

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

Forum statistics

Threads
1,136,260
Messages
5,674,697
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top