+ Reply to Thread
Results 1 to 26 of 26

How to use VLOOKUP (via Formula How-Tool)

  1. #1
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    How to use VLOOKUP (via Formula How-Tool)

    I'm really excited about sharing this! This tool guides you on how to use formula (the name, Formula How-Tool, was suggested by my dear wife. how-to sounds like how-tool.)

    So one fine day, I was just thinking about how I can help people understand how to use formulas and I thought "hey, maybe if Excel can do some animations, that would help". I googled on how to do that and that's where I learned how to do it in wiseowl's blog. I also googled to see if there are existing tools like mine and i didn't find any.

    I am pretty weak in VBA, so it took me a couple of months to turn my concept into reality. Also gotten help from a fellow excel forum member, karedog, on how to transfer variables across modules to make this work. I hope this tool really helps you to understand how to use the formulas and feel free to let me know on anything I can improve on. Read the instructions carefully & enjoy!
    Attached Files Attached Files
    Last edited by Fotis1991; 04-18-2016 at 06:00 AM.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    20,863

    Re: Formula How-Tool: VLOOKUP

    This is a great training tool, and you have a lot of work in this. I have some feedback as a first-time user. These may seem like a lot of comments, but it means I think your idea is great and worth refining. I am pretty experienced with VBA and would be happy to help in any way that you need help.

    The instructions on sheet "1" should make it clear that the user should be putting his formula in B19. Nothing happens if you put formulas in other cells. This is suggested by the text but not explicit. You might also want to lock all cells except B19 and protect the sheet.

    #3 says, "fill up all the arguments by using values from A1:D1". It is not clear what you are trying to do there. Get the user put in junk arguments to force the pop-ups to explain what's wrong? This will be confusing for most new Excel users, because they will confuse A1:D1 with what the VLOOKUP is supposed to do.

    It might be better to walk them step by step through building the formula, rather than have them put in a complete formula then diagnose it.

    The first text box that pops up says, "The blue cell is what you need to look for here." I had to go through several steps before I figured out what this meant. I don't think that is very clear. Maybe, "Your VLOOKUP formula will search for the value in the blue cell (A19)."

    I suggest putting the "OK" button directly into the text box, rather than making it a separate window.

    Column L, the double-head arrow from L3:O3, and the arrow from the text box to L3 all use the same green color which makes it a little difficult to distinguish what's going on, especially the arrow head from the text box. Think about using different colors or different shades for each distinct idea.

    Text box describing third argument: Make clear that the leftmost column, where the value is sought, is counted as column 1 in this range. (It is a common error to think, "I need to move three columns to the right for my answer, so this number is 3.)

    Sometimes when I enter a formula in B19, the blue arrow automatically comes up from the bottom and points to A18 but then stops. I have to press the "Show Guide Again" button to continue.

    Suggest "Like what I mentioned" => "As I mentioned"
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Formula How-Tool: VLOOKUP

    @6StringJazzer:
    firstly, thank you for thinking that this is a great idea worth refining! was afraid i got excited over something people dont find useful over.

    - yeah i would probably go with your suggestion to lock the cells & explicitly say they should put their formula in B19. was thinking that they might want to try copying down the formula to see how fast they can get all the results, but i guess the cons of doing that outweight the pros.

    - yes i also find it hard to convey about the A1:D1. And yes, my goal was for them to put in junk arguments to force the pop-ups to explain what's wrong. i am not sure if i can get the macro to activate once the formula is typed:
    =VLOOKUP(
    i will look into that, but what i wanted to avoid is write a lengthy explanation before they start doing the formula. i did training before and it is somehow daunting for them. they just get stuck and not even try.

    - i wanted to avoid giving direct answers like "use A19", "L:O should be your table array", "col index number is 4". i didnt want users to simply take the answers without understanding. but my wife's kind of with you on this.
    will think and consider this.

    - OK inside the textbox... good point! laziness & inability of mine. but will explore that!

    - the same colors were chosen because of the cell reference colors in 2010 (and before i think). 2013 decided to use different colors~ but yeah, i will try if different shades make a difference

    - making clear leftmost column is one should make it clearer too. roger that

    - not sure why the macro stops though. my guess is that you clicked on a cell when the macro was processing. would have to counter that too

    thanks for all the pointers! really appreciate it.
    =)

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,347

    Re: Formula How-Tool: VLOOKUP

    It is an interesting function guide. The idea could be very useful in helping people learn different functions.

    A couple of suggestions.

    1) You might include a section or discussion on "relative and absolute references." Especially with lookup functions like this, where you will usually want your "lookup_table" argument to be an absolute reference, but your "lookup_value" argument to be relative. You might include a "what happens when we copy/fill this formula down into B20:B23.
    2) I noticed that your lookup table just happens to be sorted on column 1. I entered the formula using TRUE as the fourth argument. It marked me "wrong", with its expectation that one would use FALSE for the fourth argument. I could see real value in having examples that show the difference between TRUE and FALSE in the 4th argument, and how to actually use those differences.
    Along those lines, another example might be where someone wants to search in "ranges". If lookup value is 1 to 10, return "a", if 11 to 20 return "b", and so on. I see a lot of problems on the forum that could be simplified if people understood how these "binary" search algorithms basically worked. In your example lookup table, you could use searches on "date" as a way of illustrating how this works. A lookup value of "7/1/11", with the 4th argument FALSE returns N/A, because that date is not in there. But, with the 4th argument TRUE (and the dates sorted in ascending order as they are), it can find the value for 6/23/11.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Formula How-Tool: VLOOKUP

    @MrShorty:
    thank you!

    1) I had that initially but removed it since i couldn't sync it similarly to the coding i am using for formulas. unless i want that worksheet as a read-only without animations (which people can probably google easily), i don't know if i want that

    2) yes, the worksheet was only for exact match. i felt the example wouldn't represent well for approximate match. like the eg you mentioned, the search in "ranges" would be something i think people can relate better. so i wanted to do it separately. maybe i should highlight that this VLOOKUP is only for exact match.
    =)

    having a little bottleneck with the suggestion by 6StringJazzer on putting OK in the textbox instead of MsgBox. would need the macro to stop when the OK button appears like the MsgBox does and then resume when user clicks OK. will update once i get past that!
    Last edited by benishiryo; 03-30-2016 at 06:46 AM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

    Re: Formula How-Tool: VLOOKUP

    Beni, was this the file you asked me for?
    Attached Files Attached Files
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    How to use VLOOKUP (via Formula How-Tool)

    hi Ford. no, not that. i have an excel for for this thread. and just like the "A Whole Bunch of Tips & Tutorials I've Learned" thread, i need your help to upload the latest file in the 1st post and also help me edit the title + description so that people won't get confused. i can upload here too & maybe you can help me change it in the 1st post?

    also, help me remove the google drive link & change the title to:
    How to use VLOOKUP (via Formula How-Tool)

    thanks a bunch!

    ps: thanks for sticky-ing this thread too!
    Attached Files Attached Files
    Last edited by Fotis1991; 04-18-2016 at 05:57 AM. Reason: Benny's request

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to use VLOOKUP (via Formula How-Tool)

    thanks for helping to update the file in the 1st post, Fotis!

    updates:
    - i have locked the cells which are not needed. even hidden unnecessary rows & columns - couldn't find a way to let user key in "=VLOOKUP" and start prompting guides - couldn't find a way to put "OK" inside the textbox and have the MsgBox effect that will stop the macro, and also resume the macro after pressing OK. - added remarks the leftmost column of the table_array should be column 1.
    - have disallowed cursor to be moved during the animation so that macro will not stop halfway in case of accidental clicking

    - labelled the 2 sources "Table 1" & "Table 2" to make more understandable reference.

    - highlighted the cell to fill in the formula yellow again for more understandable reference

    - explicitly ask user to use "VLOOKUP(A1,B1,C1,D1)" IF they do not know what to fill in

  9. #9
    Registered User
    Join Date
    11-19-2016
    Location
    DUNSTABLE, ENGLAND
    MS-Off Ver
    2007
    Posts
    21

    Re: How to use VLOOKUP (via Formula How-Tool)

    Hi,
    I get a runtime error 1004
    Activate method of Worksheet class failed
    Thought I'd let you know as this looks very interesting
    Well done
    Micosmiling

  10. #10
    Registered User
    Join Date
    11-19-2016
    Location
    DUNSTABLE, ENGLAND
    MS-Off Ver
    2007
    Posts
    21

    Re: How to use VLOOKUP (via Formula How-Tool)

    Hi,
    I get a runtime error 1004
    Activate method of Worksheet class failed
    Thought I'd let you know as this looks very interesting
    Well done
    Micosmiling

  11. #11
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to use VLOOKUP (via Formula How-Tool)

    hi Micosmiling. glad that you think it's interesting. i would need to know the exact steps you did that led to the error in order for me to find out more. thanks!

  12. #12
    Registered User
    Join Date
    11-19-2016
    Location
    DUNSTABLE, ENGLAND
    MS-Off Ver
    2007
    Posts
    21

    Re: How to use VLOOKUP (via Formula How-Tool)

    Hi,
    Sorry did you get any further with this tool?? I've been away.

    Micosmiling

  13. #13
    Registered User
    Join Date
    10-02-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    28

    Re: How to use VLOOKUP (via Formula How-Tool)

    Hello benishiryo!

    I'm new to this forum and I want to learn Excel. While I was browsing this forum I found this thread.

    But getting the attached error.

    Please check.

    Thanks.
    Attached Images Attached Images  

  14. #14
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to use VLOOKUP (via Formula How-Tool)

    welcome to the forum, enterdelete123! this file contains macros. To use macros in such files, you have to enable it before using. so close the file and open it again as you might have overlooked it. it looks something like this:


    if you encounter other errors in the file, do note your steps taken so that i can try to replicate it. =)
    Attached Images Attached Images  

  15. #15
    Registered User
    Join Date
    10-02-2017
    Location
    India
    MS-Off Ver
    2010
    Posts
    28

    Re: How to use VLOOKUP (via Formula How-Tool)

    I'm unable to VBA for your file. It is asking for password?

  16. #16
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to use VLOOKUP (via Formula How-Tool)

    After OK on Wlecome window and Enable Content
    right click on Welcome Tab name then unhide, select name "1", Ok and read what is there

    btw. don't click oval shapes because last one doesn't work well. If you do nothing on Tab "1" and click oval your excel file will be blocked without prompt to do something
    Last edited by sandy666; 10-03-2017 at 11:50 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  17. #17
    Registered User
    Join Date
    10-18-2012
    Location
    Dhaka
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: How to use VLOOKUP (via Formula How-Tool)

    Great Help. Tnx

  18. #18
    Registered User
    Join Date
    02-16-2014
    Location
    qatar
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: How to use VLOOKUP (via Formula How-Tool)

    i will learn from this.... somehow

    thanks a lot!!

  19. #19
    Registered User
    Join Date
    06-15-2018
    Location
    North Carolina
    MS-Off Ver
    16.14.1
    Posts
    7

    Re: How to use VLOOKUP (via Formula How-Tool)

    interesting, didnt know about this! thx..

  20. #20
    Registered User
    Join Date
    02-12-2019
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    Re: How to use VLOOKUP (via Formula How-Tool)

    Good concept, I've been using index match recently instead of vlookups. any plans for a similar guide for index match?

  21. #21
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to use VLOOKUP (via Formula How-Tool)

    when i designed this, it was catering for just 1 function. so unfortunately, i can't do it for index match.

  22. #22
    Registered User
    Join Date
    03-26-2019
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Mac Version 16.17
    Posts
    4

    Re: How to use VLOOKUP (via Formula How-Tool)

    I know this is from a long time ago, but thank you!

  23. #23
    Registered User
    Join Date
    06-15-2019
    Location
    Whitehorse, Canada
    MS-Off Ver
    microsoft office 2013
    Posts
    5

    Re: How to use VLOOKUP (via Formula How-Tool)

    Really good informative training tool!

  24. #24
    Registered User
    Join Date
    06-25-2019
    Location
    Sydbey
    MS-Off Ver
    2016
    Posts
    1

    Re: How to use VLOOKUP (via Formula How-Tool)

    Thanks for this. Unfortunately it doesn't explain the parameters that need to be filled out in the VLOOKUP function. I was lost as to what to put inside the parenthesis. If you could explain that before getting into the formulas, I think this would be a great tool.

  25. #25
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,903

    Re: How to use VLOOKUP (via Formula How-Tool)

    Quote Originally Posted by ExcelHelpPD View Post
    Thanks for this. Unfortunately it doesn't explain the parameters that need to be filled out in the VLOOKUP function. I was lost as to what to put inside the parenthesis. If you could explain that before getting into the formulas, I think this would be a great tool.
    If you are having problems defining (or understanding) a range, then perhaps you need to research that 1st, a quick google search showed there are literally 850 000 sites that explain it.

    vlookup is 1 of the simpler functions in excel.
    =vlookup(what-to-find-(a-single-cell), range-to-look-in-(a-range-consisting-or-rows-and-columns), column-number-within-range-to-return-answer-from, TRUE (data-is-sorted)/FALSE (data-is-not-sorted)

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,622

    Re: How to use VLOOKUP (via Formula How-Tool)

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help to find universal extended VLOOKUP tool
    By Remphan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2016, 03:11 AM
  2. Vlookup tool
    By crescendo in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-06-2014, 03:52 PM
  3. [SOLVED] Formula evaluation tool
    By Richard N in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2013, 04:25 PM
  4. Is VLOOKUP the right tool?
    By bwg80 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-08-2008, 12:37 PM
  5. Is VLOOKUP/HLOOKUP The Right Tool
    By ajax in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2007, 07:59 PM
  6. I am missing view tool bar from tool menu.
    By excel in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 07-04-2005, 03:05 PM
  7. Formula Tool
    By ceemo in forum Excel General
    Replies: 9
    Last Post: 05-01-2005, 04:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1