Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    vlookup querie (2003)

    Loungers,

    Is there an easy way to determine the col_index_num, other than having to count the columns, as I have a fairly wide spreadsheet with hidden columns?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup querie (2003)

    Is the simple solution out of the question: Insert a row at the top (new row 1) and number the cells across? This row could be hidden again and references made to these cells in order to get the number; or left visible.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup querie (2003)

    In addition to Paul's suggestion, you can choose a cell in the column and use COLUMN function (you will have to adjust the number to the first column of the range). This method is advantageous if you datarange will be expanding and contracting.

    Another option would be to use a combination of MATCH and INDEX. MATCH gives the row number and you can just use the INDEX based on the column of interest. This does not require an "offset" at all (this is the way if you to VLOOKUP to the left rather than the right)

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: vlookup querie (2003)

    Steve & Paul

    Thanks for your suggestions - I wonder if later versions may enable you to use the column name - ie AZ rather than a number?

    Might make things easier.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup querie (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 21-Mar-06 05:50. Added PS)</P>You can use it if you use the column function...

    Lookups do not use the absolute reference, it uses a type of "offset" from the lookup column.

    If you want to use column references, as mentioned previously, the MATCH/INDEX combo works this way.

    Steve
    PS. Checkout Re: Vlookup to the left? (Excel: 97-2002) for an example. This may be more what you are after...

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: vlookup querie (2003)

    Steve,

    Thanks for the advise - I had a look at your thread - but not all that sure - I'm using the vlookup below, how would this be modified to use the match/index combo as you suggest?

    =VLOOKUP($A$19,'Data Input Sheet'!A4:AC35,27,FALSE)

    Regards

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: vlookup querie (2003)

    =INDEX('Data Input Sheet'!AA4:AA35,MATCH(A19,'Data Input Sheet'!A4:A35,0))

    (column AA is the 27th column)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: vlookup querie (2003)

    Hans answered your question, but 1 comment (which I think is one of the advantages to the INDEX/MATCH) is that inserting/deleting columns will keep the right column linked for the lookup.

    The vlookup will not be offset correctly since the value (27 in your example) will not adjust itself.

    Other advantages of index/match is that you can VLOOKUP to left, and you can also lookup on different sheets/workbooks since the 2 ranges are independent (this could be a problem is you do not keep them "synced" with the same "rows")

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup querie (2003)

    Hans, Steve

    Thanks for your assistance

Posting Permissions

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