vlookup and VBA

[jmazorra]

Hello everyone:

I have a very large spreadsheet, about 90 or so columns. I use vlookup to reference some of those columns on another sheet. The problem I have is that every time the spreadsheet is sent to me the columns are added or deleted and therefore my vlookup does not work because is looking at the wrong column. It could be one to the left or to the right.

My question is, is there another way to do vlookup or something similar that will look at the column name rather than the number. The columns I use are never deleted, just that others I do not use are either added or deleted from the spreadsheet.

Could it be done through a VBA or formula? Or, since I only use about 10 of the 90 columns can I write a VBA that will delete all of the columns except the ones I need. While the column position changes, their names never do, that is the only constant.

Thanks.

 

[Biz]

It possible using formulas and vba too

Try using match statement with vlookup to avoid hard coding column number lookups.

Syntax

[SIZE=3][FONT=Calibri]=vlookup(lookupvalue, Range, match(columnname,column namerange,0),false)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]

Hope dis helps.

Biz

 

[jmazorra]

I think it might. I am trying to look at sheet Status Report and match the birth date column. So on my main sheet I this formula, but is not working.

=VLOOKUP($A6,'Status Report'!$A$1:$G$25000,MATCH('Status Report'!Birth date,'Status Report'!A1:CP1,0),FALSE)

where am I going wrong?

 

[Biz]

I think it might. I am trying to look at sheet Status Report and match the birth date column. So on my main sheet I this formula, but is not working.

=VLOOKUP($A6,'Status Report'!$A$1:$G$25000,MATCH('Status Report'!Birth date,'Status Report'!A1:CP1,0),FALSE)

where am I going wrong?

First 'Status Report'!$A$1:$G$25000.

Does this range need change to Report'!$A$1:$CP$25000?

Biz

 

[jmazorra]

Yes it does, I changed as below, but still not working:

=VLOOKUP($A6,'Status Report'!$A$1:CP25000,MATCH('Status Report'!Birth Date,'Status Report'!$A$1:CP1,0),FALSE)