LOOKUP function - I think

U

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
 
G

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

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top