Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: VLOOKUP

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

    VLOOKUP

    I am using VLOOKUP to find school subject names from their codes. The workbook in which I do the lookup has several named ranges, when I go to this book via the Window menu then go to the Insert menu / Names, sometimes the Paste option is available but mostly it is grayed out. Why is this? It was not a problem in XL95 where the name box was available when you went to the lookup book, but Microsoft in their wisdom eliminated this in XL97

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VLOOKUP

    Can you attach a censored example?
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: VLOOKUP

    Here is SubjectCodes.xls. Is it possible to attach 2 files to one post?
    Attached Files Attached Files

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

    Re: VLOOKUP

    SubjectCodes.xls contains the subject names and codes, and the range with all the data is named 'Codes'.

    Trial.xls contains a few codes.

    Open both files.

    If in Trial.xls, cell A2, you type "=VLOOKUP(A1," (without the "s), then go to the Window Menu and choose SubjectCodes.xls, then Insert/Names, you will find all the options grayed out. However on occasion I have found the Paste option available, and on choosing this I get a list of Range Names which I can choose from, in the present case I would choose 'Codes'. This is very convenient (and in XL95 you could do this everytime because the Name Box was available), but I cannot work out why in XL97 sometimes it is available and sometimes not.
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    When you "=VLOOKUP(A1,", and then goto the other book, try pressing F5 (or Ctrl-G) and you should get a list of named ranges, and you click on the appropriate choice. I know it is not the answer to your question, but I cannot explain the behaviour, which I have encountered myself from time to time.

    Andrew C

  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

    Wonderful! Thank you Andrew. That has frustrated me ever since I moved to XL97.

  7. #7
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    A really quick way is to press F3 to paste in the named range. I haven't ever had the greyed out stuff when using this method. As an aside, you have included the headings Code and Name in the named range (in your attachment) and this can produce incorrect results because the lookup column (A) has to be sorted to work properly. For instance I got #N/A in B1 when it looked up A1, but from B2 onwards it worked fine.

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

    Re: VLOOKUP

    Suzanna. Thanks for the tip about F3; F3 or F5 seem to work equally well in this case. If you set range_lookup to false (as I always do) the table_array does not have to be sorted. I have never had any problems caused by including the column headings in the table_array.

  9. #9
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    That sounds thrilling Michael but I'm not sure I understand exactly how you would do that (set range_lookup to false). I do lots of lookup tables and also have spreadsheets where extra info gets entered into the lookup range by other people which then goes haywire if the person forgets to sort it. Could you give an example please?

    Thanks in advance.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Suzanna:
    The "False" statement is for VLOOKUP and HLOOKUP. It is the last term in the list of arguments for the functions. As far as your problems with "where extra info gets entered into the lookup range by other people", if you define your named range using =offset($col$Row,0,0,counta($X:$X),N) (in the REFERS TO: window of the Dialog box), where $col$Row is the upper left corner of your data table, N is the width of your table, and X is a column within your data table with no entries above the column header and none below the last row of data. If $col$Row is a header for the first column of data, all your data will always be included with headers no matter how many rows others may add. You could then, for example, use paste that named range as a Pivot Table (which requires the headers). If you do not want the headers drop down one line and use the following for the Named Range: =offset($col$Row,0,0,counta($X:$X),N).
    I have probably given you more information than you wanted but I have had a few questions about how to do this and thought it would be a good opportunity to put it out there.
    Stephen

  11. #11
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Thank you very much for that info <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  12. #12
    Charlene
    Guest

    Re: VLOOKUP

    Andrew, Thx 4 the info. (post# 32102), my lookup results always report 1 column short no matter the "match type". I need my (h) values to jump to the next highest no. & can't achieve this, i.e. cols 1-5, (0-1 & 1.1-2, etc.). I have had results with index but not here. =VLOOKUP(J2,Z9:AH14,MATCH((P2/D2),Z9:AH9),0) Thx much, Chale

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Hi Charlene,

    I tried to replicate your data structures, and can see what you mean. The MATCH function works fine when specific text is being searched, but there does seem to be a problem with non integer numbers. I think it may have something to do with sorting but without actual sample data cannot really be sure. What sort of numbers are typically in D2 and P2, and what numbers are in Z9 to AH9 ?

    If you could post a sample sheet with dummy data I could see if I can locate the problem.

    Andrew

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP

    Charlene,

    Your attachment did not happen, Could you re-post.

  15. #15
    Charlene
    Guest

    Re: VLOOKUP

    Andrew,
    Attached, is a sample file of what I am trying to accomplish w/ vlookup.

Page 1 of 2 12 LastLast

Posting Permissions

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