=VLOOKUP (uhh…now what?)

I need to understand how the VLOOKUP formula reacts (or does not react) when the data in the table changes?  Is the function "volatile"?  

If the values in the "look" column (first column) change, or the table is re-sorted, will the results of the function automatically change?  If someone changes the value in the "return" column, will the function automatically retrieve the new value?  If not, how do you cause the function to recalculate?  Press F2 while on the cell with the function, press F9 while in the workbook, or other?

For non-smart users, explain named ranges.  

@Dan: That's a great question. Thanks for asking. A named range (or name) is just a word or text description that represents a range of cells

on your worksheet. The basic idea is that you can use easy-to-understand names, such as Products, to refer to hard-to-understand ranges of cells, such as Sales!C20:C30. This is especially useful when it's your job to create and maintain formulas.

To name a range, select the cells you want to refer to, type a name in the Name box at the left end of the formula bar, and press ENTER. Don't use any spaces in the name or Excel will bark at you.

Once you create a name, you can use it as the value for the table_array argument in VLOOKUP, or in other formulas and functions.

@General Ledger: I'm still investigating one particular part of your question. Will loop back soon.

- Anneliese

Heh, well I actually get named ranges.  Pretty well, in fact.  

One of the things about being the resident Excel guru at work is that I'm often approached with a 'how do I...'.  Typically, the less-then-capable user has little command over referencing another cell let alone a range of cells.  

When 'telling' people how to do it saying "ok now you need to reference this with an absolute reference which means put dollar signs everywhere", the person your telling often gets all "nooooez dragons be there", so I've taken to just straight up skipping through that and telling people how to do a named range.  

I assume - perhaps wrongly - that's a universal truth.  That one of the barriers to people making use of one of the most powerful formulas in the tool kit is the failure to understand pretty basic concepts like absolute references.  Hence why I suggested you explain named ranges along with the vlookup.  

General Ledger:  I'm pretty sure it will update automagically.  I have at least one 'living document' type spreadsheet with a a couple of tables - key on the left, and an updated variable in the right.  I'm pretty sure it updates as you would hope.  If not, I suppose I have some 'splainin to do to.

----

FWIW, when I try to teach people excel, vlookup is - without a doubt - part of the holy trinity of formulas.  

For those of you that are tired of using VLOOKUP because the column you want to reference is to the left of your starting point...

INDEX/MATCH statements are much, much more fluid and can operate like a "VLOOKUP in reverse". If you use named ranges, the syntax is easy:

=INDEX([DataTable],MATCH([Value],[Column],0))

@Dan: Ah, I misunderstood. You were offering a suggestion, not asking a question.

I’ll work your tip into the card -- it’s a good one. FYI, we’re planning to publish a beginner-level training course about VLOOKUP later this month. In the course, we do touch briefly on using a name instead of a cell reference in the table_array argument, and that fact that they’re absolute by default. I’ll let you know once that course is available on Office.com.

I often read customer comments about our help topics, and I agree that there are learning barriers, especially for the occasional user of Excel. Some of the terminology and concepts in Excel are hard to grasp at first. But once you do – whoosh, you’re off!

Out of curiosity, when you teach people about functions, which ones do you cover besides VLOOKUP?

@Brad: Nice! Thank you.

I advise people never to use VLOOKUP. Its sad to see MS promote a function which has several limitations and is not efficient

The second page of the VLOOKUP refresher could do with some sample data to better visualise the process. Something that highlighted the data being searched, an arrow denoting the vlookup identifying the value, then another arrow showing it picking up the referenced column.

A third page could show the difference in a dataset between TRUE and FALSE settings.

I created something similar once as a projection of a spreadsheet onto a white board with the participants drawing the process on the board. Worked really well.

-------

I advise people never to use VLOOKUP. Its sad to see MS promote a function which has several limitations and is not efficient

-------

Hey you kids!  Get off my lawn!

::shakes fist::

Just a simple tip for the column index which should be a number. You can use the function Column() which return a number (Column(A2) will return 1, Column(C2) will return 3).

So as example:

The fomula =VLOOKUP(A2,$D$2:$G$145, 3, 0) from the VLOOKUP Refresher can be written as: =VLOOKUP($A2,$D$2:$G$145, Column(C2), 0)

This way your formula can be copied to the columns on the right, the column index will increment, so you can retrieve several columns from the second table with one only formula. Note the $ in front of A2 to fix the Lookup value in column A

Giff & Serge & Dan -- thanks so much for the excellent feedback. I'm going to revise the refresher card later this month based on some of these suggestions. I think showing the visual is particularly important.

Thanks for creating this VLOOKUP Refresher. It is a nice piece of work.

That said, it would be interesting to see why people are searching on VLOOKUP. Is it because they can't recall the parameters OR is it because they can't get it to do what they want? Given Excel's Intellisense is really nice now days I believe the majority would be due to the later reason.

Constraints like the need to have the Index as the leftmost column & possibly the data in sorted order can require you to make design compromises &/or spend hours looking up help in your attempt to get VLOOKUP to do something it wasn't designed to do.

Perhaps you could also do a card on MATCH/OFFSET & mention it in your VLOOKUP guide.

Serge:  that tip is awesome.  

hi, by circumventing #NA, do you man how to avoid those as a result of Vlookup?

if so I do this

If(iserror(vlookup(...)), "does not exsist", vlookup(...))

David, you raise an interesting point. It would be ideal for us to be more explicit about when VLOOKUP isn't the right design choice.

For example, we could add a "Before you begin" section to VLOOKUP Help topic that redirects people who really need to use a different solution--e.g., "Is the value you're looking up in the leftmost column of your lookup table? If not, and you can't move it, you need to use a different set of functions, and here's where to start..."

I'll bring this back to my content team for discussion. I think it can extend beyond VLOOKUP to other high-use business functions. Thanks for the idea.