Results 1 to 6 of 6

Thread: VLOOKUP (Any)

  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP (Any)

    OK - this is a history of Excel question!!

    Can anyone tell me WHY when you specify the COL INDEX NUMBER value with the VLOOKUP function you use the coloumn number in the table rather than just the column letter?

    Thanks, Laurie

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

    Re: VLOOKUP (Any)

    If the lookup table starts in column A, the column letter for the column in which to look corresponds to the actual column header: the third column is C, etc.

    But if the lookup table starts, say, in column D, and you want to retrieve a value from the third column of the table, which letter do you want to use? C because it is the third column in the table, or F because that is the "physical" column? The first may lead to confusion, the latter makes it difficult to move the lookup table to another location in the worksheet.

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

    Re: VLOOKUP (Any)

    I would suspect that it is because it makes it easier to used a value in a cell to decide which column in the table to use. It would be much harder to do a two dimensional lookup if you had to calculate a cell address in the cell specifying the column.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (Any)

    Yes Ithis is basically what I had thought.

    It just doesn't seem to be consistent with other features of Excel. And from a user point of view it would be much easier to be able to just select the column, especially if the table has lots of columns (as ours do).

    Maybe it's just the way Microsoft built it!

    Thanks for your help, Laurie

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

    Re: VLOOKUP (Any)

    But if the column is determined by some data, just selecting the column is very difficult if you need to calculate an A1 style reference. If this is a big problem, then you can use something like this (assuming your table is in A1100 and you want to return the value in column D):

    <pre>=VLOOKUP(E1,$A$1:$D$100,COLUMN(D1)-COLUMN(A1)+1,FALSE)
    </pre>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (Any)

    This is one reason I prefer to use the INDEX MATCH technique for anything but the simplest of lookups. I really don't like VLOOKUP because if you add a column to the middle of your table, you could end up with the wrong answer.

    =INDEX(AC1:AC100,MATCH(A1,Q1:Q100))
    is equivalent to
    =VLOOKUP(A1,Q1:AC100,n) [I'm too lazy to calculate n]

Posting Permissions

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