VLOOKUP problem 1

[lole]

Hi
I am a secondary school teacher in the UK and am responsible for setting academic targets for pupils.I have put together a lookup table (shown below)in order to extract target national curriculum targets for year 7 (col 2), year 8 (col 3) and year 9 (col 4) given a GCSE target (col 1) but VLOOKUP doesn't like this table at all! I guess because of it is impossible for all columns to be in ascending order. I would be grateful for any suggestions as to how I might achieve this. I'm not worried if I do not use VLOOKUP, any other function or workaround will be fine if I can get the job done.

Many thanks

Lole

A 6a 7b 8c
A/B 6b 7c 7a
B 6c 6a 7b
B/C 5a 6b 7c
C 5b 6c 6a
C/D 5c 5a 6b
D 4a 5b 6c
D/E 4b 5c 5a
E 4c 4a 5b
E/F 3a 4b 5c
F 3b 4c 4a
F/G 3c 3a 4b
G 2a 3b 4c
G/U 2b 3c 3a

 

[xlbo]

columns don't need to be in ascending order - use FALSE as the 4th argument in the vlookup formula - I'd like to meet whoever has been teaching vlookup with 3 args 'cos this comes up all the time (grrrrr). FALSE as the 4th argument returns an exact match or #N/A if it can't find an exact match - no sorting is required.
So for your table
=vlookup("C/D",tablerange,2,false) will give an answer of 5c

HTH
Geoff

 

[lole]

Thanks Geoff for the quick reply. I have been using the 4th arguement (logical value) but although
=vlookup("C/D",tablerange,2,false) does return 5c
=vlookup("D",tablerange,2,false) returns #N/A

I've tested all permutations and the pattern is column 1 values "x/y" always return a valid value, column 1 values "x" always return #N/A EXCEPT (!) in the "G" row where it returns a valid value! This threw me somewhat.


Any thoughts would be appreciated

Lole

 

[xlbo]

Sounds like the values in your lookup column have extra spaces on the end.
test the length of the cell by using =len(a1)
where A1 contains the 1st entry in your table. My guess is you won't get 1, you'll get a 2 or 3, indicating an extra space at the end of the letter (can also check by highlighting the cell and going into the formula bar and pressing End)
To cure this, either use the trim function on all the values in your Grade column and then copy and paste as values over the originals o, being as it's only a short list, go into all the cells and manually delete the spaces from the end
HTH
Geoff

 

[lole]

Geoff

Brilliant! You were right. I would never have got there on my own. I've been stuggling with this for a week or more. It even had my network manager flummoxed!

Thanks!

Lole