Equating input in one column to values in the next column

[PP]

In column H any one of the following values could be inputted:

am
pm
am pm
1
2
3
4
5
6

as they are inputted a value should appear in column I based on the following 'scoring' system:

am=1, pm=1, am pm =2, 1=2, 2=4, 3=6, 4=8, 5=10, 6=10

I tried setting up a VLookup table in another sheet called punishment table to hold these values, the first column starting at A2 and second column starts in B2.


1 2
2 4
3 6
4 8
5 10
6 10
am 1
am pm 2
pm 1

Am I going about this in the right way? Can't seem to get it to return the right value in column I.

 

[erik.van.geit]

Hi,

use LOOKUP instead of VLOOKUP
   A     B  
 2 1     2  
 3 2     4  
 4 3     6  
 5 4     8  
 6 5     10 
 7 6     10 
 8 am    1  
 9 am pm 2  
10 pm    1  

table
[Table-It] version 06 by Erik Van Geit
  H  I  
2 2  4  
3 4  8  
4 6  10 
5 am 1  

output
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
I2:I5 =LOOKUP(H2,table!A2:A10,table!B2:B10)

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[PP]

Tried this:

=VLOOKUP('Punishment Table'!A2:A10,'Punishment Table'!A2:B10,'Punishment Table'!B2:B10,FALSE)

when H8=am and it returned am instead of 1

 

[erik.van.geit]

when posting a solution it's nice to get feedback

 

[PP]

Sorry Erik - didn't notice you had posted on the thread. I will try your solution and feedback

 

[PP]

Using your solution I tried to modify it as my table was set up in a sheet called punishment table, that's why it's taken me so long to respond:

=LOOKUP(H8,'Punishment Table'!A2:A10),'Punishment Table'!B2:'2.10.06'!)

but this returns an error

 

[erik.van.geit]

why do you have
'2.10.06'
in your formula ?
this cannot work: you need a cellreference (or a named range)