Excel gurus, help!

Factual Questions
1

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:

CHART 1
Anderson | BIO
Bloom | CHEM
Charles | CHEM
Johnson | LING
Stevens | HIST
CHART 2
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?

2

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
3

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

4

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.

5

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.