Help me Excel!

February 4, 2009 8:45 AM

I-don't-know-what-I'm-talking-about-filter: Excel 2003 VLOOKUP help, please!

This is the formula that is in cell D2 of "sheet two":
=VLOOKUP('sheet one'!D2,'sheet one'!W2:X9,2,FALSE)

I want cell D3 of "sheet two" to be this:
=VLOOKUP('sheet one'!D3,'sheet one'!W2:X9,2,FALSE)

See how the cell in "sheet one", D3 changed, but the W2:x9 part stayed the same? When I select the cell and grab the little doohickey in the corner to copy the formula to the rest of cells in the same column, it changes the W2:x9 part as well as the D2,3 etc part. I did warn you I have no idea what I'm talking about.

How do I easily change one part of the formula for the rest of the cells in the column without changing the other part of the formula? I've been copy/pasting and changing each cell individually, but this is making me emotionally unstable. I am hoping that someone who knows far more than I about Excel will be able to guide me through this difficult time.

posted by dogmom to Computers & Internet (4 answers total)

Best answer: If you don't want a cell reference to change, you have to put dollar signs in front of it, like so :

$W$2:$X$9
posted by desjardins at 8:51 AM on February 4, 2009

Response by poster: Oh snap. I feel like an idiot. This is the problem with being self taught- the teacher has no credentials.

I checked some previous excel threads, and I am going to pick up some of the books that others have mentioned.

Thank you for your quick answers, though! You made my job today much easier!
posted by dogmom at 9:14 AM on February 4, 2009

Another way to do this is to create a named range. The easiest way to do this would be to select W2..X9 with your mouse, then go into the box in the upper left hand corner that has the Cell Name in it(Probably W2 in your case). Then just type in a name for the range of cells (you could even use "lookup".I do all the time). Then, your formula could read
VLOOKUP('sheet one'!D2,lookup,2,FALSE). When you copy it, it will keep the name "lookup" in there.
posted by smithygreg at 9:42 AM on February 4, 2009

A related tip you may not know. Make sure that the index column (probably the W column for you on sheet one) has data that is sorted or alphabetized.

That's only the case if the final, boolean value in the VLOOKUP function is TRUE. If it's FALSE, as it is here, VLOOKUP looks for an exact match to the lookup value, and the index column does not need to be sorted.
posted by DevilsAdvocate at 10:11 AM on February 4, 2009