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

1. ## 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

2. ## Re: Strange Vlookup Behavior

Originally Posted by bencar
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

3. ## Re: Strange Vlookup Behavior

Originally Posted by Peter_SSs
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!