Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Adding formula to Vlookup

    I have the following formula in E5 =VLOOKUP($C5,$C$1:E$3,3,FALSE). I have copied this formula into cells E6 to E15 and it gives me the correct results

    I would like to amend the formula to count the column number so that this is inserted automatically. I need to copy the fomula from E5 to E15 to F5 to L15 and I have to change the column offset from say , 3 to , 4 to , 5 etc each time I copy the formula to a new column . for e.g. =VLOOKUP($C5,$C$1:E$3,3,FALSE) needs to change to =VLOOKUP($C5,$C$1:E$3,4,FALSE) when copied to F5

    Your assistance in this regard is most appreciated

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Have managed to sort out problem

    =VLOOKUP($C5,$C$1:E3,column(e1),FALSE)

  3. #3
    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
    FWIW, it would be more efficient to do the lookup part once, then refer to that in several INDEX formulas:

    In say B5: =MATCH($C5,$C$1:$C$3,0)
    then:
    =INDEX(E$1:E$3,$B5)
    and copy across.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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