Nested IF Excel problem

[grego9]

I have a problem in Excel 2000. I am trying to return a value in Column F based on what is Column E. There are about 14 different text variants in Column E (all look like "2008H1", "2008H2)). Depending on the variable in column E I want a different columns value (eg column P, S, T etc) returning in Column F for that row.

The nested IF function didn't work as I was limited to 7 variants. I tried doing something called criteria - which would allow me to add in two criteria - but I didn't really know what I was doing! (code below)

ActiveWorkbook.Names.Add Name:="Criteria1", RefersToR1C1:="=IF('Shelf Allocations'!E2="2008H2",P2,IF('Shelf Allocations'!E2="2009H1",S2,IF('Shelf Allocations'!E2="2009H2",V2,IF('Shelf Allocations'!E2="2010H1",Y2,IF('Shelf Allocations'!E2="2010H2",AB2,IF('Shelf Allocations'!E2="2011H1",AE2,IF('Shelf Allocations'!E2="2011H2",AH2,IF('Shelf Allocations'!E2="2012H1",AK2))))))))"

ActiveWorkbook.Names.Add Name:="Criteria2", RefersToR1C1:="=IF(E2='2012H2',AN2,IF(E2='2013H1', AQ2,IF(E2='2013H2',AT2,IF(E2='2014H1',AW2,IF(E2='2 014H2',AZ2,IF(E2='2015H1',BC2,IF(E2='2015H2',BF2,I F(E2='2015H2',BI2))))))))"

ActiveCell.FormulaR1C1 = "=IF(Criteria1,Criteria1,Criteria2)"

This didn't work either.

I also tried creating a look up in column F that looked up value E against a look up table like the one below:

2008H1 =P2
2008H2 =S2

I tried to get the macro to paste the =P2 bit into column F - but it only recognised it as text and not a formula - so this didn't work either.

any ideas please I'm going mad!!!!!

[ubentook]

The following uses the "Switch" function. There are other ways to do this.
'--
Sub TheOneIWant()
ActiveSheet.Range("F1").Formula = MatchThis(ActiveSheet.Range("E2").Value)
End Sub

Function MatchThis(ByRef E2Value As String) As String
MatchThis = Switch(E2Value = "2009H1", "=S2", _
E2Value = "2009H2", "=V2", E2Value = "20010H1", "=Y2")
End Function