Benefits of Vlookup

[jskushawah]

I recently faced an interview, there he (Interviewer) ask me: "What are the benefits of vlookup?"

can someone specify this question?

 

[Faseeh]

Hi jskushawah,


The benefits are obvious and can be found here:


http://chandoo.org/wp/2010/11/01/vlookup-excel-formula/


..a disadvantage is that it always keep looking to the right of the lookup column, it gives an error when you try to look to the left of lookup column. Here is where INDEX(MATCH()) step in.


Regards,

 

[jskushawah]

Thanks Faseeh!

can u please share your email ID with me.

jsk.mis.info@gmail.com is my ID.

 

[Faseeh]

Hi jskushawah,


You are welcome, my email id is faseeh10@hotmail.com


Faseeh

 

[Hui]

Faseeh, All


Vlookup can indeed look to the left!


It requires a little bit of thinking but it is explained here in a Comment by reader Mike:

http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/#comment-140892

 

[Faseeh]

Hi Hui,


Thank you that's a really good trick.


Faseeh

 

[SirJB7]

Hi, all!


Checking Mike's trick and trying to test it I found an issue when downloaded the sample file "lookup-to-left.xls" from:

http://img.chandoo.org/f/vw/lookup-to-left.xls

and placed this formula in any empty cell:

=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)


The problem is with array:

{2,1}


In my Spanish Excel version if I left the comma as separator I get displayed a #¡REF! error -as I expected- as it evaluates the CHOOSE function as:

{1592;1088;1680;2133;1610;1540;1316;1799;1624;726;2277;714;2682}

Net sales values only, not Sales person.


I changed the comma to a semi-colon, my natural list and parameters separator, getting a #N/A error -unexpectedly- and the CHOOSE evaluated to this:

{1592;"John";#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}


So I switched to VBA editor, Immediate window pane, and entered this statement:

ActiveCell.Formula="=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)"

and guess what I got displayed as formula in Excel window?... this:

=BUSCARV(1088;ELEGIR({21};$B$5:$B$17;$D$5:$D$17);2;0)

Note the array part, separated by backslash:

{21}


And then I got displayed "John", the correct answer.


Hope it helps.


Regards!