Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: varying ranges for vlookup

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How can I create a vlookup statement with the range as a variable? In other words, I need the "range_array" portion of the statement to change according to certain conditions.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ...and what would those conditions be?

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have named multiple named ranges for different years. Therefore, if I have a date given in 2001 elsewhere in the worksheet, I need the vlookup to look at the "2001 range".

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    bp,

    if there's a logical sequence to your multiple named ranges, this shouldn't be a problem......

    I set up some called :

    yr2000
    yr2001
    yr2002
    yr2003

    etc

    this allowed me to VLOOKUP on a cell (say containing the word "Sammi" then in the range array I was able to interrogate the date that was sitting on "Sammi"s row

    (say 13 May 2002)

    as follows :

    =YEAR(A1)

    which brought back "2002"

    since that corrosponds with the logic of my named ranges, I popped it into an indirect as part of the range array as follows :

    =VLOOKUP(A1,INDIRECT("yr"&YEAR(B1)),1,0)

    where A1 held "Sammi"
    where B1 held "13/05/2002"
    and the named range of year 2002 was "yr2002"
    :: Pharma Z - Family drugstore ::

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  6. #6
    New Member
    Join Date
    May 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris,

    That did exactly what I needed!

    Thank you very much for your assistance!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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