Vlookup Help Beginner

[d488z]

Hello - First of all thanks for taking the time to read this post..

I am trying to do a Vlookup for example look at Col Eng which has a 3- then look at Baseline find the 3- in the list and output what is in the next columns, the out put I am looking for is 1= (and the rest of that line)....3- 1=,1+,2-,2=,2+

I am trying this =VLOOKUP(A2,D2:I33,2,FALSE) which sort of works but when I try to stretch the formula to the next column I get 3= which is incorrect. Much appreciated.


[TABLE="width: 611"]
<tbody>[TR]
[TD]Eng[/TD]
[TD]Mat[/TD]
[TD]Sci[/TD]
[TD]Baseline[/TD]
[TD]Year 7 Term 3[/TD]
[TD]Year 8 Term 3[/TD]
[TD]Year 9 Term 3[/TD]
[TD]Year 10 Term 3[/TD]
[TD]Year 11 Term 3[/TD]
[/TR]
[TR]
[TD]3-[/TD]
[TD]2+[/TD]
[TD]2=[/TD]
[TD]9=[/TD]
[TD]6-[/TD]
[TD]6+[/TD]
[TD]7=[/TD]
[TD]8=[/TD]
[TD]9=[/TD]
[/TR]
[TR]
[TD]4+[/TD]
[TD]4+[/TD]
[TD]4+[/TD]
[TD]9-[/TD]
[TD]5+[/TD]
[TD]6=[/TD]
[TD]7-[/TD]
[TD]8-[/TD]
[TD]9-[/TD]
[/TR]
[TR]
[TD]2+[/TD]
[TD]2=[/TD]
[TD]2[/TD]
[TD]8+[/TD]
[TD]5=[/TD]
[TD]6-[/TD]
[TD]6+[/TD]
[TD]7+[/TD]
[TD]8+[/TD]
[/TR]
[TR]
[TD]2+[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]8=[/TD]
[TD]5-[/TD]
[TD]5+[/TD]
[TD]6=[/TD]
[TD]7=[/TD]
[TD]8=[/TD]
[/TR]
[TR]
[TD]1-[/TD]
[TD][/TD]
[TD][/TD]
[TD]8-[/TD]
[TD]5-[/TD]
[TD]5=[/TD]
[TD]6-[/TD]
[TD]7-[/TD]
[TD]8-[/TD]
[/TR]
[TR]
[TD]2=[/TD]
[TD][/TD]
[TD][/TD]
[TD]7+[/TD]
[TD]4+[/TD]
[TD]5+[/TD]
[TD]6-[/TD]
[TD]7-[/TD]
[TD]7+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7=[/TD]
[TD]4+[/TD]
[TD]5=[/TD]
[TD]6-[/TD]
[TD]6+[/TD]
[TD]7=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7-[/TD]
[TD]4=[/TD]
[TD]5-[/TD]
[TD]5+[/TD]
[TD]6=[/TD]
[TD]7-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6+[/TD]
[TD]4=[/TD]
[TD]5-[/TD]
[TD]5+[/TD]
[TD]6-[/TD]
[TD]6+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6=[/TD]
[TD]4-[/TD]
[TD]4+[/TD]
[TD]5=[/TD]
[TD]5+[/TD]
[TD]6=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6-[/TD]
[TD]4-[/TD]
[TD]4+[/TD]
[TD]5-[/TD]
[TD]5=[/TD]
[TD]6-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5+[/TD]
[TD]3+[/TD]
[TD]4=[/TD]
[TD]4+[/TD]
[TD]5-[/TD]
[TD]5+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5=[/TD]
[TD]3=[/TD]
[TD]4-[/TD]
[TD]4=[/TD]
[TD]4+[/TD]
[TD]5=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5-[/TD]
[TD]3=[/TD]
[TD]4-[/TD]
[TD]4=[/TD]
[TD]4+[/TD]
[TD]5-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4+[/TD]
[TD]3-[/TD]
[TD]3+[/TD]
[TD]4-[/TD]
[TD]4=[/TD]
[TD]4+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4=[/TD]
[TD]2+[/TD]
[TD]3=[/TD]
[TD]3+[/TD]
[TD]4-[/TD]
[TD]4=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4-[/TD]
[TD]2=[/TD]
[TD]3-[/TD]
[TD]3=[/TD]
[TD]3+[/TD]
[TD]4-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3+[/TD]
[TD]2-[/TD]
[TD]2+[/TD]
[TD]3-[/TD]
[TD]3=[/TD]
[TD]3+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3=[/TD]
[TD]1+[/TD]
[TD]2=[/TD]
[TD]2+[/TD]
[TD]3-[/TD]
[TD]3=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3-[/TD]
[TD]1=[/TD]
[TD]1+[/TD]
[TD]2-[/TD]
[TD]2=[/TD]
[TD]2+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2+[/TD]
[TD]1-[/TD]
[TD]1=[/TD]
[TD]1+[/TD]
[TD]2-[/TD]
[TD]2=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2=[/TD]
[TD]1-[/TD]
[TD]1=[/TD]
[TD]1+[/TD]
[TD]2-[/TD]
[TD]2=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2-[/TD]
[TD]W4[/TD]
[TD]1-[/TD]
[TD]1=[/TD]
[TD]1+[/TD]
[TD]2-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1+[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]1-[/TD]
[TD]1=[/TD]
[TD]1+[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1=[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]1-[/TD]
[TD]1=[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1-[/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]1-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]U[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[philwojo]

I think you just need to lock your cell references, try this:

[COLOR=#333333]=VLOOKUP($A$2,$D$2:$I$33,2,FALSE)[/COLOR]

Then update the "2" to be the next column you want, so in your 2nd formula it would be 3, then 4 etc.

 

[KennyGreens]

Hi, I'm not sure how you're going about stretching the formula, but it would work better using absolute/mixed references. For example (each formula assumes you are only using A2 as the lookup value, change the referencing accordingly):

=VLOOKUP($A$2,$D$2:$I$33,2,FALSE)
=VLOOKUP($A$2,$D$2:$I$33,3,FALSE)
=VLOOKUP($A$2,$D$2:$I$33,4,FALSE)

This should work as, and this is just an assumption, it seems that you dragged the formula 1 cell to the right, which changed the formula in that cell to =VLOOKUP(B2,E2:J33,2,FALSE), which if you see loooks for "2+" in the Year 7 Term 3 column and gives an answer of 3= in the next column.

 

[d488z]

Hi Thanks for your reply. This is what I did in the end =VLOOKUP(A2,EAP,2,FALSE). I named the range EAP but the same thing. I was hoping to you use the stretch or double click as I will have about 200 entries that I need to do multiply by 5 and then I have 13 subjects. What I am trying to do is map Targets for students through school.

I think you just need to lock your cell references, try this:

[COLOR=#333333]=VLOOKUP($A$2,$D$2:$I$33,2,FALSE)[/COLOR]

Then update the "2" to be the next column you want, so in your 2nd formula it would be 3, then 4 etc.

 

[philwojo]

Just update the formulas in your first row, then you can just copy down each column to all rows.