Formula help needed

[AbbieJade]

Hi,

Really need some help with trying to work out a formula. I have created an excel spreadsheet which includes the table included in the link below (Table 2. A DIF Analysis Algorithm on page 35 (41/393)) of the following pdf:

<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Lucida Grande'; color: #2b2f2f } span.s1 {font-kerning: none} </style>https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/607717/20170404-JSP_822_Part_2-Final-O.pdf

The spreadsheet has a drop-down menu so the difficulty (D), importance (I), and frequency (F) can be selected. However, I can not seem to work out a formula which once the (DIF) has been selected will work out the training category. Any help or pointers would be really appreciated.

Thanks

 

[etaf]

page not found
how do you know the training category
is there a table of dif v training
is so - a lookup

 

[AbbieJade]

Wayne,

Thanks the training category is worked out by reading across the table:

D - Very Difficult
I - Not important = Trg Cat 2
F- Infrequent

Thanks

 

[etaf]

=LOOKUP(A2,{"D","I","F"},{"Very Difficult","Not Important","Infrequent"})

where a2 is the dropdown

for i = what to return, Not important or Trg Cat 2