Results 1 to 7 of 7

Thread: Vlookup (2000)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup (2000)

    How do I (or CAN I) write a vlookup formula that will allow the source data range to automatically expand if necessary?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Use a name for your source data, when you reach Step 2 of the Pivot Table Wizard press F3 and select the name of your source data, then proceed as usual.

    Also see This Thread for automatically expanding ranges

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Now I'm totally confused - I'm not making a pivot table - I'm just writing a vlookup formula on one sheet telling it to look at a range of data on another sheet and would like to know how I can write the formula so that when I add either new rows or columns to the source range that data is automatically recognized by the vlookup formula.

    Help!

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Yes, but you should be able to use the same techniques used there to create a dynamic named range. Then you can use that named range in your VLOOKUP.
    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Thanks. I decided that, for my purposes, it will be much simpler for me to tell my students that when they need a range expanded in a lookup formula to just select the sheet with the formulae, and globally search and replace the current range with the new range.

    Thanks again, though - I'll use your suggestion myself one day.

    Linda

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Sorry AuntLinda, my fault, I was confused as much the same technique works in PTs as in VLOOKUP.

    Suppose you want to VLOOKUP into cell C2 something based on the entry in cell A2 of your sheet.

    Name the range where you want to find the data, lets call it MyData.
    In cell C2 type:

    =VLOOKUP(A2,

    Now press F3, this will bring up a box with MyData (and any other named ranges in your workbook), click on MyData then OK. The Formula Bar will now contain:

    =VLOOKUP(A2,MyData

    Just finish this off in the usual way, i.e.

    =VLOOKUP(A2,MyData,col_index_num,range_lookup).

    If the named range is in another workbook, which should be open, you will have to go to the Window menu and select that workbook before pressing F3.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2000)

    Seems like "confused" is the state of most people these days - I think I've surpassed confused as a temporary state and permanently moved to the "state of confusion" - anyway, this is perfect - again, my sincere thanks.

    Have a great week

    Aunt Linda

Posting Permissions

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