Results 1 to 6 of 6
Thread: Vlookup automation (Excel 2003)

20090616, 01:45 #1
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
I am having a huge database in Excel and use all possible formulas to manage the reports. One formula I frequently use is "=IF(ISNA(VLOOKUP($B19,[Book1.xls]GR'!$B$26:$Q$92,3,0)),0,VLOOKUP($B19,[Book1.xls]GR'!$B$26:$Q$92,3,0))". My problem is that I have to copy the formula in every column and than change the column No. manually. Is there any way to make Excel understand to update the Col Index No on every change? It will save a lot of time as well as energy.
Regards
PrasadRegards
Prasad

20090616, 02:03 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
I assume that you're referring to the third argument of VLOOKUP. You may be able to use the COLUMN() function. This returns the column number of the cell containing the formula.
Let's say that the formula that you mention is in cell E19 (i.e. in column 5), and that you want a similar formula in F19 (in column 6) with 4 as column index, etc. As you see, the column index for the formula is 2 less than the column number of the cell with the formula. So you could use COLUMN()2 as column index:
IF(ISNA(VLOOKUP($B19,'[Book1.xls]GR'!$B$26:$Q$92,COLUMN()2,FALSE)),0,VLOOKUP($B19,'[Book1.xls]GR'!$B$26:$Q$92,COLUMN()2,FALSE))

20090616, 02:14 #3
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='780025' date='16Jun2009 08:03']I assume that you're referring to the third argument of VLOOKUP. You may be able to use the COLUMN() function. This returns the column number of the cell containing the formula.
Let's say that the formula that you mention is in cell E19 (i.e. in column 5), and that you want a similar formula in F19 (in column 6) with 4 as column index, etc. As you see, the column index for the formula is 2 less than the column number of the cell with the formula. So you could use COLUMN()2 as column index:
IF(ISNA(VLOOKUP($B19,'[Book1.xls]GR'!$B$26:$Q$92,COLUMN()2,FALSE)),0,VLOOKUP($B19,'[Book1.xls]GR'!$B$26:$Q$92,COLUMN()2,FALSE))[/quote]
Making some sense. Let me try & I am sure it will work.Regards
Prasad

20090616, 02:27 #4
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='prasad' post='780026' date='16Jun2009 08:14']Making some sense. Let me try & I am sure it will work. [/quote]
Thanks Hans, whenever I need, you are always there. Thanks a lotRegards
Prasad

20090617, 04:27 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 203 Times in 187 Posts
You can also use a combination of INDEX and MATCH which is more efficient if you return multiple values from one row in the lookup table. For example, in a cell (let's say C19) you can enter:
=MATCH($B19,'[Book1.xls]GR'!$B$26:$B$92,0)
to return the match position. Then you can use this number in several INDEX formulas  for example:
in D19: =IF(ISNA($C19),"",INDEX('[Book1.xls]GR'!D$26$92,$C19))
and copy across to return values from column E, F, G etc.Regards,
Rory
Microsoft MVP  Excel

20090617, 06:38 #6
 Join Date
 May 2008
 Location
 India
 Posts
 306
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='rory' post='780263' date='17Jun2009 09:27']You can also use a combination of INDEX and MATCH which is more efficient if you return multiple values from one row in the lookup table. For example, in a cell (let's say C19) you can enter:
=MATCH($B19,'[Book1.xls]GR'!$B$26:$B$92,0)
to return the match position. Then you can use this number in several INDEX formulas  for example:
in D19: =IF(ISNA($C19),"",INDEX('[Book1.xls]GR'!D$26$92,$C19))
and copy across to return values from column E, F, G etc.[/quote]
What we can do with excel depends upon application of out brain. I must apply my own brain first. Thanks to update.
Regards
Prasad
Regards
Prasad