Strange Vlookup Behavior

[bencar]

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
Book Title	Author	Price	Type
A Game of Thrones	George R.R.	$19.77
Go to Sleep	Adam Mansbach	$8.22
A Dance with Dragons	George R.R.	$18.81
The Hunger Games	Suzanne Collins	$4.94
The Original Argument	Glenn Beck	$7.99
Heaven is for Real	Todd Burpo	$9.34
Unbroken	Laura Hillenbrand	$13.99
Smokin' Seventeen	Janet Evanovich	$15.21
In the Garden of Beasts	Erik Larson	$13.78
Catching Fire	Suzanne Collins	$8.97

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="align: right"]hardcover[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]

[TD="align: right"]hardcover[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]

[TD="align: right"]hardcover[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]9[/TD]

[TD="align: right"]paperback[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]10[/TD]

[TD="align: right"]hardcover[/TD]

</tbody>

Sheet4
​

 

[Peter_SSs]

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

 

[bencar]

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!