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:="Criteria 1", 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,I F('Shelf Allocations'!E2 ="2011H1",AE2,I F('Shelf Allocations'!E2 ="2011H2",AH2,I F('Shelf Allocations'!E2 ="2012H1",AK2)) ))))))"

ActiveWorkbook. Names.Add Name:="Criteria 2", RefersToR1C1:=" =IF(E2='2012H2' ,AN2,IF(E2='201 3H1',AQ2,IF(E2= '2013H2',AT2,IF (E2='2014H1',AW 2,IF(E2='2014H2 ',AZ2,IF(E2='20 15H1',BC2,IF(E2 ='2015H2',BF2,I F(E2='2015H2',B I2))))))))"

ActiveCell.Form ulaR1C1 = "=IF(Criteria1, Criteria1,Crite ria2)"

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.Ran ge("F1").Formul a = MatchThis(Activ eSheet.Range("E 2").Value)
End Sub

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