Vlookup or vba or code

[Vishaal]

Thanks in advance

I am using the following vlookup

Lookup value = C10
Table array =sheet1!$B$2:$GKX$5
Col index = 5041
Range lookup=0

Now problem is
I want to change the table array "sheet1!$B$2:$GKX$5" for coloumn from B to GKW

And also change the coloumn index accordingly, like 5040, 5039

 

[Vishaal]

I mean in coloumn E
Lookup value = C10
Table array =sheet1!$B$2:$GKX$5
Col index = 5041
Range lookup=0

Coloumn F
Lookup value = C10
Table array =sheet1!$C$2:$GKX$5
Col index = 5040
Range lookup=0

Coloumn G
Lookup value = C10
Table array =sheet1!$D$2:$GKX$5
Col index = 5039
Range lookup=0

 

[Fluff]

Just drop the column index number by 1 & you should be sorted.

 

[Vishaal]

i don't want to do it manually

 

[Vishaal]

hi

i mean i want don't want to copy paste vlookup code on each column (because we have large number column, From E to GKX)

If we do it manually we need to change the in "Table array =sheet1!$C$2:$GKX$5", in every column C,D,E,F,G,H it will take more time

Also pls guide how can we paste it to in every column raws, i mean raw 11 to raw 420 in every column

 

[mohadin]

Hi
for column index
in column E use 5046-COLUMN()
then drag up to the column you want

I hope its is clear

 

[mohadin]

Any good !!!!

 

[Vishaal]

its not working, we need to change two points

(1) Table array =sheet1!$C$2:$GKX$5
Col index = 5040

(2)sheet1!$D$2:$GKX$5
Col index = 5039

(3)sheet1!$E$2:$GKX$5
Col index = 5038

(4)sheet1!$F$2:$GKX$5
Col index = 5037

till the GKX and col index 2

 

[mohadin]

Can you tell where is the cell for each(1)(2)(3)....

 

[mohadin]

Hi;
Tel you what
See this and you manipulate to you requirement

="sheet1!"&ADDRESS(2;COLUMN()-3)&":"&"$GKX$5"
Good luck