How can I use (IF) formula to pull data from list?

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

Can someone help me with a formula that will do the following?

I have a list of courses (Algebra 1, Algebra 2, etc) and I have course
codes (2032, 2033, etc).

Different courses will be listed In my spreadsheet in column A. I
want column B to automatically populate the correct course code form a
list (perhaps on a hidden sheet like I use in Data Validation Drop
Down lists).

For example, Algebra 1 is entered in A3 and then the Algebra 1 course
code would appear in B3.
If Algebra 2 is entered in A4, then a different code will
automatically appear in B4.

Auto populating the course code will save a lot of work and also make
sure the correct course code is always entered as different people
will be completing my spreadsheets.

I know this can be done, but I'm not sure how to go about doing it.

Thanks for your help. I have 15 courses and 15 course codes.
 
VLOOKUP is the function to use.  Of course, you have to ensure that
your course name is entered correctly.

Suggested set-up:

1.  Name a worksheet Courses and in A1:Bn have a list of courses in
Column A, with the corresponding course code adjacent in column B.

2.  On Sheet1, A1:An (where n is equal to at least the number of
entries you want in column A):
        Select the range
        Select Data/Data Validation
              Allow:    List
              Source:  =Courses!$A$1:$A$n  (where n is the number of
courses)
        (ignore blank and in-cell dropdown can also be selected).
          you can pretty this up with other options; pop-up messages;
etc.

        B1:  =IF(A1="","",VLOOKUP(A1,Courses!$A$1:$B$n,2,FALSE))

Select B1 and fill down to Bn- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you! This worked perfectly and I love this forum. I am saving
this solution in my "How To" folder. :) Thanks again.
 
Back
Top