Vlookup automation (Excel 2003)

[prasad]

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
Prasad

[HansV]

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))

[prasad]

[quote name='HansV' post='780025' date='16-Jun-2009 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.

[prasad]

[quote name='prasad' post='780026' date='16-Jun-2009 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 lot

[rory]

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.

[prasad]

[quote name='rory' post='780263' date='17-Jun-2009 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