Problem with VLOOKUP using table name?

[dmcfaul]

Hi everyone,

I'm having trouble with a tracking sheet I created for my department in school to track pupil scores.
Specifically the second sheet.
The teacher can use a dropdown box (under tracking name) to see all the pupils % scores and the residual between that and the class average.

Unfortunately it seems to not pick up the correct score.

However I have checked my formula and it is definitely pointing at the right cell.

(I have added a sample of the worksheet using fake names.)

Any help would be appreciated.

[dmcfaul]

Some additional info, when the worksheet is locked teachers can only change the cells coloured orange.
The first sheet is data entry.
The second sheet has the lookup function.
The third sheet graphs the data from the second sheet.

[jason.b75]

You need to use exact match, not approximate.

=VLOOKUP($A$3,Tracking!$A$6:$BL$36,3,0)

However, I would suggest a different method, this formula in B3, then dragged right will match the tracking point as well so that you don't need to change the lookup column in each formula.

=INDEX(Tracking!$C$6:$BL$36,MATCH($A3,Names,0),MATCH(B$1,Tracking!$A$1:$BJ$1,0))

[dmcfaul]

Thank you Jason, That formula works brilliantly.
Could you maybe explain how you put it together? I'm always trying to further my Excel knowledge.
I understand that INDEX is for looking up an ARRAY but I have never used a MATCH formula before.

[jason.b75]

The match function simply returns the position of whatever you're looking for rather than the actual result that you would get from vlookup.

The first match is used to find the row that contains the selected pupil name, the second finds the column with the tracking point. The index function then gives you the result where the row and column intersect.

Note that the row, or column found by the match function is based on the range used, not the actual row numbers, using your example file, the match for Donovan Heras is row 2, then second row in A6:A36.

Lastly, with the column match, the range is offset to the left of the index range by 2 columns. This is done because the results are coming from columns C,G,K, etc. but the Tracking point headings are in columns A,E,I, etc. When you merge cells, whatever you enter into them only exists in the leftmost cell, the others are effectively empty.

Not so much out of the box thinking, but you need the lid open.

[dmcfaul]

Thanks for the additional info - will certainly be using this in the future!