Strange Vlookup Behavior with COLUMNS Function

[sayme]

Hi guys,

Got a question about vlookup with columns functions. I use, VLOOKUP($B16,$B$4:$D$13,COLUMNS($B:B)+1,0) and I paste the formula across cells C16 and D16 to return matches of author and price. But when I use B$16 instead of $B16 in the formula, it returns a match in author but not price. If it returns a match in author why cant it return a match in price??

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=16365C]#16365C[/URL] , align: center"]Number[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=16365C]#16365C[/URL] "]Book Title[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=16365C]#16365C[/URL] "]Author[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=16365C]#16365C[/URL] "]Price[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]A Game of Thrones[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]George R.R.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$19.77[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Go to Sleep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Adam Mansbach[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$8.22[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]A Dance with Dragons[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]George R.R.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$18.81[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]The Hunger Games[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Suzanne Collins[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$4.94[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]The Original Argument[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Glenn Beck[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$7.99[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Heaven is for Real[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Todd Burpo[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$9.34[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Unbroken[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Laura Hillenbrand[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$13.99[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Smokin' Seventeen[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Janet Evanovich[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$15.21[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]In the Garden of Beasts[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Erik Larson[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$13.78[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Catching Fire[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] "]Suzanne Collins[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=538DD5]#538DD5[/URL] , align: right"]$8.97[/TD]

</tbody>

Sheet2​

 

[Peter_SSs]

But when I use B$16 instead of $B16 in the formula, it returns a match in author but not price. If it returns a match in author why cant it return a match in price??

Because when you make that change and then copy the formula from column C to column D it becomes

=VLOOKUP(C$16,$B$4:$D$13,COLUMNS($B:C)+1,0)

This formula is trying to look up what is in column C (the author - returned from the formula in C16) in column B, which contains book titles, not authors.

 

[yaroslav89]

That is because the $ sign is an absolute reference. Meaning no matter where you put your formula it will always reference to a pecific column or a row. So, $B absolute column and $16 absolute row. So when you use B$16 and drag formula/or copy formula the result for author is correct, but for an amount reference changed to C$16. You want to keep $B16 to keep absolute reference on your column B since it will not change in this case and keep row (16) as is.

 

[jtakw]

Hi,

If you Want to use your VLOOKUP in the manner you described:

Instead of =VLOOKUP($B16,$B$4:$D$13,COLUMNS($B:B)+1,0)

Changed to =VLOOKUP(B$16,$B$4:$D$13,COLUMNS($B:B)+1,0)

Then you would Also need to change $B$4:$D$13 to B$4:$D$13 and COLUMNS($B:B) to COLUMNS($B:$B)

So now the formula will look like =VLOOKUP(B$16,B$4:$D$13,COLUMNS($B:$B)+1,0)

But since COLUMNS($B:$B) is basically a constant of 1, you would No longer need that function,

So the formula can just be =VLOOKUP(B$16,B$4:$D$13,2,0)

Now you can drag Either modified version of your VLOOKUP formula from Column C to Column D and get correct results for Arthur and Price.

See below for comparison, All C Column formulas copied to D:

Book1
	A	B	C	D
3	Number	Book Title	Author	Price
4	1	A Game of Thrones	George R.R.	$19.77
5	2	Go to Sleep	Adam Mansbach	$8.22
6	3	A Dance with Dragons	George R.R.	$18.81
7	4	The Hunger Games	Suzanne Collins	$4.94
8	5	The Original Argument	Glenn Beck	$7.99
9	6	Heaven is for Real	Todd Burpo	$9.34
10	7	Unbroken	Laura Hillenbrand	$13.99
11	8	Smokin' Seventeen	Janet Evanovich	$15.21
12	9	In the Garden of Beasts	Erik Larson	$13.78
13	10	Catching Fire	Suzanne Collins	$8.97
14
15
16		Go to Sleep	Adam Mansbach	8.22
17			Adam Mansbach	8.22
18			Adam Mansbach	8.22
Sheet142

Cell Formulas
Range	Formula
C16	=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B:B)+1,0)
C17	=VLOOKUP(B$16,B$4:$D$13,COLUMNS($B:$B)+1,0)
C18	=VLOOKUP(B$16,B$4:$D$13,2,0)

 

[Peter_SSs]

Now you can drag Either modified version of your VLOOKUP formula from Column C to Column D and get correct results for Arthur and Price.

I disagree. I have used your two modified formulas in rows 17 & 18 as suggested below, but they do not return the correct results when dragged to column D for price.

(Perhaps I'm not understanding, but I'm not sure why the OP is wanting to change a formula that seems to be doing the job perfectly as it is. )

Excel Workbook
	B	C	D
3	Book Title	Author	Price
4	A Game of Thrones	George R.R.	$19.77
5	Go to Sleep	Adam Mansbach	$8.22
6	A Dance with Dragons	George R.R.	$18.81
7	The Hunger Games	Suzanne Collins	$4.94
8	The Original Argument	Glenn Beck	$7.99
9	Heaven is for Real	Todd Burpo	$9.34
10	Unbroken	Laura Hillenbrand	$13.99
11	Smokin' Seventeen	Janet Evanovich	$15.21
12	In the Garden of Beasts	Erik Larson	$13.78
13	Catching Fire	Suzanne Collins	$8.97
14
15
16	Catching Fire	Suzanne Collins	8.97
17		Suzanne Collins	4.94
18		Suzanne Collins	4.94
Formula