Excel gurus, help!

I have 2 excel charts of different lengths. One has a list of students and their majors. Another chart has a list of student classes. It looks basically like this:

Anderson | BIO
Bloom | CHEM
Charles | CHEM
Johnson | LING
Stevens | HIST
Anderson | History of Africa
Anderson | Calculus
Anderson | French
Anderson | Sociology
Bloom | Advanced Basketweaving
Bloom | Calculus
Charles | Singing
Charles | Calculus
Charles | Woodworking
Charles | Italian
Johnson | Sociology
Johnson | Logistics
Johnson | Business
Stevens | Marketing

I’m trying to get one chart that combines the information – basically having the major listed as a third column. I’ve tried writing an IF formula but I can’t quite get it to work out since the lists are of a different length. Basically try to get it to repeat majors as long as the student name remains the same. Maybe I’m going about it the wrong way.

Any ideas?

The VLOOKUP function will solve your problem.

It basically:

  • searches down a column for a matching value in a “list” (Excel-speak for a table)
  • returns a cell value from the same row as the match

If your first list (the majors) started in E1, and the second list (classes) started in A1, then:

  • put the formula =VLOOKUP(A1,$E$1…$F$5,2,FALSE) in C1. It should show “BIO”.
  • copy this formula down the column

To add to what K364 said, make sure your column E is in alphabetical order, otherwise you may get inocrrect results.

FANTASTIC! :smiley:

Works like a charm. Thanks so much guys. This could have made the difference between a project taking 10 minutes or 10 hours.

Actually, the “FALSE” part of the function (known as the “Range_Lookup” option) says “Find an exact match”. When you use VLOOKUP this way you do not have to have the lookup table sorted.

However, if Range_Lookup is TRUE, then VLOOKUP will return the value closest, but not exceeding the search value. For this to work properly the lookup table must be sorted.