Assume Items are in cells I1:I14 on sheet2, you could do it like this:

=INDEX(Sheet2!I$1:I$14,CODE(B1)-64)

Hope this helps.

Pete

On Jul 5, 6:04*pm, stevejeffries
wrote:
I am trying to write a formula, but, have got stuck as I haven't used Excel
for a very long time. Hope someone can help me with this one - it is for my
son's school and is based on the game of Cluedo:-
Sheet 1 - *column 1 will be *a list of names (suspects)
* * * * * * * *column 2 will be an item (clock, chair etc)
* * * * * * * *column 3 will be a location (playground, office etc).
So, A1 might read 'Mr Smith', B1 might read 'clock', C1 might read
'playground' - meaning that Mr Smith was hit with a clock in the playground.

On sheet 2 I have one column listing 14 items and one column listing 14
locations.
The formula I want is possibly an 'IF', but, I'm not sure. I want to be able
to type letters A - N in cells in column B and get a return from the list of
'items' on sheet 2, i.e. If I type 'A' in cell B1 I want it to return
'Computer' in the cell.

Can anyone help please?