• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COLUMNS function in VLOOKUP

N

Narend

Member
why we use column function with +1 or +2 in vlookup formula.

can not we retrieve the same data from not applying column function?


narend
 
In VLOOKUP table the first column is Lookup Column, so we actually want data from 2 column onwards. So, COLUMNS($B4:B4) give result as 1 adding 1 to it will give 2 and if copy right side we will get 3,4, and so on.

That is the reason to add 1 to it.

You can also use like this:
=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4),0)
This will generate 2, 3 and so on.

EDIT: I had edited the title of the thread as this is COLUMNS function and not COLUMN function. Both are different.

Regards,
 
Somendra Misra said:
In VLOOKUP table the first column is Lookup Column, so we actually want data from 2 column onwards. So, COLUMNS($B4:B4) give result as 1 adding 1 to it will give 2 and if copy right side we will get 3,4, and so on.

That is the reason to add 1 to it.

You can also use like this:
=VLOOKUP($B16,$B$4:$D$13,COLUMNS($B4:C4),0)
This will generate 2, 3 and so on.

EDIT: I had edited the title of the thread as this is COLUMNS function and not COLUMN function. Both are different.

Regards,
Click to expand...



Thanks for responding so quickly.

but can not we get the data from the usual proceedure or this is another proceedure to retrive the data. (i.e Columns way)


narend
 
You can get by hard coding the values as 2,3,4 but when you will drag the formula you will have to edit the formula manually every time. This is avoided by this trick.

Regards,
 
Somendra Misra said:
You can get by hard coding the values as 2,3,4 but when you will drag the formula you will have to edit the formula manually every time. This is avoided by this trick.

Regards,
Click to expand...


Thanks and now it is clear to me.


Thanks again for this kind support.

Narend
 
You must log in or register to reply here.
Back
Top