Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    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
    Prasad
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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))

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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.
    Regards
    Prasad

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
    Regards
    Prasad

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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
    Regards
    Prasad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •