Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

=VLOOKUP($C$4&$C$5&$C9,Labels!$D:$E,2,FALSE)
share|improve this question

1 Answer

up vote 6 down vote

& 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

Then:

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
1  
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.... – David Vandenbos 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
2  
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. – David Vandenbos Aug 30 '12 at 21:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.