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!

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"

@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.
=)

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.

@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!

Beni, was this the file you asked me for?

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!

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

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!

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. =)

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

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

Originally Posted by ExcelHelpPD

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)

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

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar or even the same to this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 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