LOOKUP Function not working?

Hi all, I am having issues with quite a simple LOOKUP function on my Spreadsheet. I would appreciate some help, if possible.

I am trying to create a simple LOOKUP function (cell E4 that collects data from D4) that takes a value from a range of D,M,P,N,U (Distinction, Merit, Pass, Near Pass and Unclassified) and reply a result of a point system 8, 6, 4, 2, 0.

D = 8
M = 6
P = 4
N = 2
U = 0

When I use this code =LOOKUP(D4,'Data Sheets'!A1:A5,'Data Sheets'!B1:B5) all of the grades work correctly and return the correct data apart from N which returns 6.

Can someone please help. The spreadsheet it attached.

Thanks in advance.

Here's the problem...

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

You list

D = 8
M = 6
P = 4
N = 2
U = 0

But that's not what is in your spreadsheet
U 0
NP 2
P 4
M 6
D 8

You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

LOOKUP require lookup_vector must be in A-Z order; the list should be:
D
M
NP
P
U

With your lookup issues , just using VLOOKUP instead
=VLOOKUP($E$4,'Data Sheets'!$A$1:$B$5,2,0)*F4

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread