Results 1 to 6 of 6

excel vlookup column limit?

This is a discussion on excel vlookup column limit? within the Excel Questions forums, part of the Question Forums category; Is there a limit to the number of columns one can reference in excel Vlookup? I can get numbers from ...

  1. #1
    kwg
    kwg is offline
    Board Regular
    Join Date
    Apr 2003
    Posts
    72

    Default excel vlookup column limit?

    Is there a limit to the number of columns one can reference in excel Vlookup? I can get numbers from a table up to 26 columns but when I try to get any other column greater than 26 I get the #REF error. Thank you Happy new year!

  2. #2
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: excel vlookup column limit?

    This might help about column limits:

    http://www.mrexcel.com/board2/viewto...t=column+limit

    "Don't Ruin an Apology with an Excuse"...

  3. #3
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: excel vlookup column limit?

    Can you post you formula?
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  4. #4
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default Re: excel vlookup column limit?

    Quote Originally Posted by santeria
    This might help about column limits:

    http://www.mrexcel.com/board2/viewto...t=column+limit

    santeria...I really don't see how a thread about the limitations of XL's column count corresponds to the OP's questions about VLookups?

    As far as the OP's question...from what I've seen there is not a "limitation" on the column that a VLookup can return. If you are getting a #REF error, it is because you are trying to return a column outside of your table_array.

    For example...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book5___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    3*Tommy3
    2
    #REF!*MrExcel1
    3
    **Aladin2
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in A1 is =VLOOKUP("Tommy",C1:D3,2)
    The formula in A2 is =VLOOKUP("Tommy",C1:D3,3)

    Now, the second formula is returning an error because I am trying to return column 3 of a table that only contains 2 columns.

    Also...this is quoted directly from the XL Help documentation...
    Quote Originally Posted by Microsoft Excel Help
    Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

  5. #5
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: excel vlookup column limit?

    I had thought so, but obviously I was wrong ...



    Quote Originally Posted by TommyGun
    santeria...I really don't see how a thread about the limitations of XL's column count corresponds to the OP's questions about VLookups? [/quote]
    "Don't Ruin an Apology with an Excuse"...

  6. #6
    kwg
    kwg is offline
    Board Regular
    Join Date
    Apr 2003
    Posts
    72

    Default Re: excel vlookup column limit?

    Thank you for the help everyone-problem is resolved I expanded the vlookup range as recommended in replies. Happy New Year!

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
  •  


DMCA.com