Vlookup formula when data is moved

[Angela.fitt]

Is there an easy way to either set the formula or change is when the lookup data is moved from one column to another? I would like the formulas to reference a column name instead of column number.

 

[kuldeepjainesl]

Hi Angela.fitt,


Try using match function in Vlookup for coloum index.


For example


=VLOOKUP(B2,$A$2:$C$16,MATCH($E$1,$A$1:$D$1,FALSE))


Where

B2=Lookup value

A2:C16= Lookup array

A1:D1 = Header Row

E1= Header value of the coloum expected to use for return the answer.


Regards,

Kuldeep

 

[Faseeh]

Hi Angela.fitt,


You can also use "Named Ranges". If you Name a range and then move that to some other location you formula will not be affected.


...and welcome to the Formus BTW!!!


Regards,

Faseeh

 

[nazmul_muneer]

Think in the Product_Cost sheet you have 3

Code Brand Name Product Type

1001 AAA A1

1002 BBB B1


and your Report sheet you want to bring Product Type on the basis of Code

In this sheet you have the same 3 columns

ColumnA ColumnB ColumnC

Code Brand Name Product Type

1002

In the B3 type the formula given below for showing Brand Name

=VLOOKUP($A2,Product_Cost!$A$1:C$50,MATCH(B$1,Product_Cost!$A$1:$C$1,0),0)