# Excel gurus, help!

**URL:** <https://boards.straightdope.com/t/excel-gurus-help/257157>\
**Category:** Factual Questions\
**Created:** [July 27, 2004, 9:29pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157 "2004-07-27T21:29:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jayrot](https://avatars.discourse-cdn.com/v4/letter/j/3ec8ea/32.png) [@Jayrot](https://boards.straightdope.com/u/Jayrot)\
**Post date:** [July 27, 2004, 9:29pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157/1 "2004-07-27T21:29:44Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [July 27, 2004, 10:21pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157/2 "2004-07-27T22:21:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Gangster\_Octopus](https://avatars.discourse-cdn.com/v4/letter/g/a8b319/32.png) [@Gangster\_Octopus](https://boards.straightdope.com/u/Gangster_Octopus)\
**Post date:** [July 27, 2004, 10:33pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157/3 "2004-07-27T22:33:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Jayrot](https://avatars.discourse-cdn.com/v4/letter/j/3ec8ea/32.png) [@Jayrot](https://boards.straightdope.com/u/Jayrot)\
**Post date:** [July 27, 2004, 10:37pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157/4 "2004-07-27T22:37:50Z")

</div>

FANTASTIC! 😃

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

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [July 27, 2004, 10:47pm UTC](https://boards.straightdope.com/t/excel-gurus-help/257157/5 "2004-07-27T22:47:04Z")

</div>

> [@Gangster Octopus](#):
>
> To add to what K364 said, make sure your column E is in alphabetical order, otherwise you may get inocrrect results.

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.