Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I pretty much understand the purpose of the VLOOKUP function but I don't understand the function value that I encountered in a spreadsheet.

The first parameter is supposed to be the value to look up, but I expected a literal or a cell reference. What the heck is $C$4&$C$5&$C9 referring to in the following example?

share|improve this question

& is the symbol for Concantenation. It simply "adds" the values from those three cells (C4, C5, C9) together into a single value. It can be used to combine numbers or text.

For example, assuming:

        A  B  C
Row1    1  2  3
Row2    X  Y  Z


A1&B1&C1 = 123
A2&B2&C2 = XYZ

Note: You can treat the first example (A1&B1&C1) just like any other number and perform calculations against it.

Your VLOOKUP calculation is combining those three cells and looking for that combined result in your lookup range.

share|improve this answer
Ah. I didn't recognize $C$4 as a reference to the C4 column. I expected it to be written simply as C4. Is this an alternate syntax? – Chad Aug 30 '12 at 20:36
Good question: the $ means an absolute reference. So no matter where you move or copy the formula, it will always look at the cells C4 and C5, but it won't always look to C9-it could reference C10, C11, C12.... – dav Aug 30 '12 at 21:03
$ is used to prevent the cell pointers from changing as you drag the cell contents to other cells. For example, $A1 will allow you to drag vertically and produce $A2, $A3 and so on, but dragging horizontally will stay $A1 (not $B1, etc). In other words, no matter where you drag or fill that cell with the formula, it will always point at the same locations for the lookup. – techturtle Aug 30 '12 at 21:03
FWIW, $ is a core syntax for you to get comfortable with. It can make your life easy or drive you crazy depending upon the circumstances. – dav Aug 30 '12 at 21:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .