Strange Vlookup Behavior
Results 1 to 3 of 3

Thread: Strange Vlookup Behavior
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Strange Vlookup Behavior

    Hi guys,

    This vlookup problem has me scratching my head. For the following table I use the vlookup formula below to lookup up book title, author and price in different cells at the same time. It includes a COLUMNS function to paste it across cells to return book title, author and price. Whats so strange about it is, I can enter COLUMNS($Z:Z) instead of COLUMNS($A:A) and I still get the right answers. Why does it do this if column Z isn't in the lookup table but works as good as column A which is?

    =VLOOKUP($B13,$A$2:$E$11,COLUMNS($A:A)+1,0)


    A B C D E
    1 Number Book Title Author Price Type
    2 1 A Game of Thrones George R.R. $19.77 hardcover
    3 2 Go to Sleep Adam Mansbach $8.22 paperback
    4 3 A Dance with Dragons George R.R. $18.81 paperback
    5 4 The Hunger Games Suzanne Collins $4.94 paperback
    6 5 The Original Argument Glenn Beck $7.99 hardcover
    7 6 Heaven is for Real Todd Burpo $9.34 paperback
    8 7 Unbroken Laura Hillenbrand $13.99 hardcover
    9 8 Smokin' Seventeen Janet Evanovich $15.21 paperback
    10 9 In the Garden of Beasts Erik Larson $13.78 paperback
    11 10 Catching Fire Suzanne Collins $8.97 hardcover
    Sheet4
    Last edited by bencar; Apr 7th, 2018 at 09:39 PM.

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,382
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Strange Vlookup Behavior

    Quote Originally Posted by bencar View Post
    I can enter COLUMNS($Z:Z) instead of COLUMNS($A:A) and I still get the right answers. Why does it do this ..
    =VLOOKUP($B13,$A$2:$E$11,COLUMNS($A:A)+1,0)
    Because COLUMNS($A:A) returns 1 and COLUMNS($Z:Z) also returns 1
    If you drag it across one column..
    COLUMNS($A:B) returns 2 and COLUMNS($Z:AA) returns 2
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Strange Vlookup Behavior

    Quote Originally Posted by Peter_SSs View Post
    Because COLUMNS($A:A) returns 1 and COLUMNS($Z:Z) also returns 1
    If you drag it across one column..
    COLUMNS($A:B) returns 2 and COLUMNS($Z:AA) returns 2
    Makes sense now.. thanks!

Some videos you may like

User Tag List

Tags for this Thread

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
  •