Lookup problem

[scj0409]

Hi,

I'm a teacher and I'm using Excel to convert levels into a numerical value so that I can get an average.

My problem is that my LOOKUP is returning the wrong result. My lookup array and match type is this table:

1c 1
1b 2
1a 3
2c 4

Etc.

The table goes from 1c - 8a, numbers 1- 24.

So in my cell N2 the grade entered is a '7b', however, the LOOKUP is converting it to the number 21 instead of 20 as it should be according to the table.


My formula is this: =LOOKUP(N2,Sheet2!D2:D26,Sheet2!C2:C26)

Thanks for your help.

 

[wideboydixon]

Welcome to the forum. I'm a little confused which was round your data on Sheet2 is organised. Are the codes in column C or D? Either way, you'd be better to use INDEX/MATCH for an exact match. The equivalent of the formula above is:

=INDEX(Sheet2!$C$2:$C$26,MATCH(N2,Sheet2!$D$2:$D$26,0))

You might need to swap C and D if they are indeed the other way around. One other option is to remove the lookup altogether and have:

=LEFT(N2,1)*3+FIND(MID(N2,2,1),"cba")-3

Hope that helps.

WBD

 

[Kaper]

if your
1c
1b
1a
2c

is in column C of sheet 2 and these values are numbered 1,2,3....

use just

=MATCH(N2,Sheet2!$C$2:$C$26,0)

 

[jammoca]

your array vector is not currently in ascending order.

for lookup to work, it needs to be ascending.

Chris

 

[scj0409]

your array vector is not currently in ascending order.

for lookup to work, it needs to be ascending.

Chris

Hi.

Thanks for this.

On the marking scale though a '1c' is worth less than a '1b', so on this scale this is ascending if you understand what I mean. Is there a way to get this to work another way?

TIA

 

[jammoca]

Hi TIA,

trust me,

highlight your lookup range of ... C2:D25

click on the 'Data' tab at the top of the spreadsheet

click 'Sort'

click 'Sort by Column C' ... order ... 'A to Z'

and use the following formula ..... =LOOKUP(N2,C2:C25,D2:D25)

I guarantee it will produce the correct answer for you ... 20

 

[Jonmo1]

Assuming the grades (1c 1b etc) are in column C, while the numbers are in D

=VLOOKUP(N2,Sheet2!$C$2:$D$26,2,0)

 

[jammoca]

agreed, but sjc doesn't seem to know to sort the left column into ascending order.

 

[Jonmo1]

Vlookup with 0 as the 4th argument doesn't require data to be sorted.

 

[wideboydixon]

With the uniformity of the mapping, this is still a better solution I think

=LEFT(N2,1)*3+FIND(MID(N2,2,1),"cba")-3

WBD