LOOKUP function - I think

[UK Mike]

Hi

I am trying to set up a worksheet that allows me to type in a question
number in one cell (that the student has got wrong) and in the next cell
gives the answer. Tried looking through the help pages but not sure which
formula to use. I assume that the formula should be in the second cell. I
have about 150 questions I want to use and idealy the answers would be in
another sheet (although not necessarily - they can be just in another
column). For example:

If I type "1" in column A, column B would then say "25" (the answer to
question 1), if I type "2" in column A, column B would then say "36" (the
answer to question 2), etc.

Hope you guys can help.

Thanks a lot.

Mike

 

[Gord Dibben]

On sheet2 in Column A is a list of numbers 1 to 150

In column B is a list of question answers corresponding to the numbers in
column A

On sheet1 in B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$150,2,FALSE)

Copy down as far as you wish.

Enter numbers in Column A of sheet1 to return question answers from Sheet2


Gord Dibben MS Excel MVP

On Wed, 1 Apr 2009 15:13:01 -0700, UK Mike <UK

 

[T. Valko]

Set up a column with the answers to the questions. Let's assume there are 25
questions so there would be 25 answers. Let's assume the 25 answers are in
the range X1:X25.

A1 = answer number you want (from 1 to 25)

This formula entered in B1 will return the corresponding answer:

=INDEX(X1:X25,A1)

You might want to make it more robust by only recognizing values in A1 that
are in fact the numbers 1 to 25.

=IF(AND(INT(N(A1))=A1,A1>=1,A1<=25),INDEX(X1:X25,A1),"Invalid Entry")