30 responses

  1. Cal Caliva
    March 30, 2012

    I really enjoy your blog, and read it everyday it’s published.

    Can you explain the Boolean concept behind the use of “+”, “-“, and “*” with sum and sum product?

    In your range look-up above what is the significance of the “–” in the first part of the formula.

    Thanks

    Reply

  2. ruve1k
    March 30, 2012

    @Cal,
    The SUMPRODUCT function takes numbers as its arguments. When you do a comparison operation on a range (B6:B15<=C3) then it returns an array of boolean values (TRUE?FALSE). In order to convert the boolean values into numbers you need to do some sort of mathematical operation on them. For example –(B6:B15<=C3) or (B6:B15<=C3)+0 or if you have to boolean arrays you can just multiply them together like so (B6:B15=C3).
    In this example –(B6:B15=C3) the — is completely unnecessary.

    Reply

  3. ruve1k
    March 30, 2012

    Reposting because the text editor combined the negative signs.

    For example – -(B6:B15<=C3) or (B6:B15<=C3)+0 or if you have to boolean arrays you can just multiply them together like so (B6:B15=C3).
    In this example – -(B6:B15=C3) the – – is completely unnecessary.

    Reply

  4. CMC
    March 30, 2012

    VLOOKUP “can go left”: please see Richard Schollar’s article at

    http://vlookupweek.wordpress.com/2012/03/27/richard-schollar-vlookup-left/

    Best regards,

    CMC

    Reply

    • Subhash
      April 2, 2012

      Thanks CMC.
      Didn’t know about this trick.

      Reply

  5. Giga_Me
    March 30, 2012

    Another awesome article. Vlookup with wildcards will also work with lists within a single cell.

    For example, if I want to find 54321 in a list of numbers within cell D8 like this 46116, 49162, 19048, 54321 the following formula will work: =VLOOKUP(“*”&54321&”*”,$D$8,1,0).

    Hope this helps someone else as much as it did me.

    Reply

  6. ChicagoPete
    March 30, 2012

    GREAT EXCEL TIPS!

    I like using VLOOKUP with the lookup criteria on the one sheet and all the data on multiple (sometimes hidden) sheets, broken up by category – so the end-user has a clean, “where did it get that data from” experience. Takes a bit more to code, but well worth it.

    Cheers!

    Reply

  7. Glen
    March 30, 2012

    I find that lookup (not vlookup or hlookup) used with vector is very useful, very simple, and overcomes the “no left” constraint of vlookup. I often wonder why more people do not use it.
    In lookup you specify a variable (or its address), a lookup column, and a result column. For more complex formulae you may even use a nested formula for the first parameter (the others are trickier since the lookup and result ranges must correspond).
    The only advantage that vlookup has is that you can use “Range_Lookup” to specify exact or near match. However, in many things (such as social security number, isbn etc., the variable is already exact. So the advantages generally outweighs the disadvantages. For example if you insert a column that changes your index_column number, the you have to edit your vlookup formula – not so with lookup. Lookup is also easier to copy with mixed fixed and relative reference (since the column number is not required).

    Reply

  8. CMC
    April 1, 2012

    Altough to obtain the correct value, the lookup vector in

    LOOKUP(lookup_value,lookup_vector,result_vector)

    must be in ascending order. See:

    http://support.microsoft.com/kb/324986/en-us

    Best regards,

    CMC

    Reply

  9. Don
    May 18, 2012

    I receive your blog daily and refer to/read the article often.  Your postings and resources are so helpful!  THANK YOU!

    Reply

  10. Shabbir
    July 4, 2012

    Hi, Mr. Chandoo, you are amazing, thank you very much for sharing the information, evertime I receive a mail from you there is something special to learn.  Once again thans and keep up the good work, best wishes to you and your family.

    Reply

  11. XLCalibre
    July 6, 2012

    I prefer combining INDEX and MATCH. It’s less demanding on the system as they are both non-volatile functions: http://xlcalibre.com/xl-formula-focus-redundancy-calculations-using-index-and-match/

    That said, VLOOKUP can be a good alternative to nested IFs. An example that I often use is VLOOKUP for converting from one currency to another: http://xlcalibre.com/xl-formula-focus-vlookup-for-currency-conversion/

    Reply

  12. sushanta
    October 23, 2012

    Dear Chandu,

    I could not find a article on how to do a vlookup between two workbooks.

    Pls share the link if already posted or request you share the trick. 

    Reply

  13. Marina
    December 14, 2012

    Wow – thanks for taking the time to create this resource! This is the first time I’ve found any help type page that can teach me new things about a feature I’ve used for longer than I can remember. 🙂

    Reply

  14. Alex
    March 22, 2013

    Vlookups and sumifs can also made to lookup column values that have concatanated. I’ve used this often.

    Reply

  15. Jan
    March 24, 2013

    I think I am becoming a little bit more awesome everytime I read this blog and the tips others post. I’m starting way below most of you I think, so I have a lot of room for more awesomeness 🙂  Thank you everyone!

    Reply

  16. Greg Collett
    April 17, 2013

    Not sure if this is elsewher here so I will let you all have it anyway.

    HLOOKUP can be bomb-proofed like this:

    =HLOOKUP(Value, Range(B20:AZ500), Row($B$45)-Row($B$19),False)

    This means you want to get the value for the subject in row 45 for say, the year, in Value. The Row()-Row() technique replaces the hard coded row offset so that you can add additional rows above the item you are looking for. Row-Row automatically adjusts the offset.

    For Vlookp, use column()-column().

    Cheers

    Greg

    Reply

  17. Karyn
    September 4, 2013

    Hi, I have two tab in a workbook, I want to do a vlookup from one tab another with name data, for the result I want either the word check if the name is on both tabs or blank if it is not. Can anyone help with the formula. Thanks.

    Reply

  18. TWY
    June 2, 2014

    Thanks for this post.

    Reply

  19. Ali
    August 22, 2014

    I am encountering some problem with vlookup. When I type the vlookup formula the first result that it yields is “#N/A”, but as I drag it, it yields perfect result. please help me out.

    Reply

  20. Francis
    March 18, 2015

    =IF(valSalesPerson=$B$4,0,VLOOKUP(valSalesPerson,tblData,3,FALSE)) What is the use of putting the if condition in the above formula?

    Reply

  21. Christina Smith
    January 9, 2016

    Your a genius man… I am enjoying exploring about excel and you help me a lot on your tips every time you send an email… more power and success… thank you…

    Reply

  22. burak
    July 4, 2016

    I am using vlookup as a listing for example name and phone number list. And adding an extra call box and answer box. When you type the name the number appears automatically. And more you can do

    Reply

  23. M. Rimbao
    September 5, 2016

    Thanks alot! Very much appreciated.

    Reply

  24. Shripad
    April 25, 2017

    This is fantastic, Unbelievable, IN my case also it started with Vlookup only.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop