easy VLOOKUP question

TDK_Man
TDK_Man used Ask the Experts™
on
Hi,i have created a spreadsheet in MS Excel, and on one sheet, i have a list of students surnames (A2 - A36), and in cells B2 - B36 i have the list of first names.

In cells H2 - H36, i have a list of their email addresses.

What i want to do is that in sheet2, i want to be able to type in a students name, and have the email address come up.
The only other problem is that some students have the same surnames, like "Smith", so, i would want all the "Smiths" email addresses to be displayed.

I need to use VLOOKUP in the formula, but have no idea on what to type in!
I hope someone can help me, thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
East Coast Manager
Commented:
You won't get a vlookup to return "multiple" values.

I suggest that you insert another column C on sheet1 (which you can then hide if necessary). In it, use this formula:

=B2&" "&A2

Then, on sheet2, in cell B2, put:

=vlookup(A2,Sheet1!C2:Sheet1!H36,6,0)

In Sheet2 A2, you'll put John Smith (first AND last name).
Posted again.  

This is a duplicate question of:
http://www.experts-exchange.com/Applications/Q_20400484.html

TDK Man,
  You should notify the experts that you posted the question to multiple locations via a reference from one to the other with a value of zero points.  That way while experts peruse the system, you obtain greater exposure to your question, yet you would accumulate the answers in one thread instead of multiple threads.  I have posted my comments again here to continue this thread in this forum.
 



TDK Man,

If you're set on using the vlookup formula here is a possible solution for you.  On Sheet 2 use cell A1 for your entry cell, i.e. where you type "Smith" by your example.  In cell A2 place the following formula:
=IF(ISNA(VLOOKUP($A$1,Sheet1!$A2:$H2,8,FALSE)),"",VLOOKUP($A$1,Sheet1!$A2:$H2,8,FALSE))
copy this formula down to cell A36 on Sheet 2.

This will produce a column of blank cells for those items that do not match the name in A1, and the email address for those items that do match the name in A1 in the position that they appear on Sheet 1.

The advantage of using the formulas in this manner would be that if you change the name in cell A1 the email addresses would be looked up accordingly.

I do not see any other method other than those similar to this one to return the results of more than one surname that match the last name using the vlookup formula.  Another function or macro would probably be better suited to your needs.

Understanding your objectives might help us provide you with a better alternative.  The auto filter comes to mind quickly, but I don't know what end result you desire.  A little more information would be helpful.

Hope this helps.

Commented:
You might try using the validation tools to achieve your result.

First, name the email list "Emails".  Then add a column to your first sheet that contains:

=a1&", "&b1

That will give you a list of students that shows both last and first names.

Assign that list a name, e.g., "WholeNames"

Then, on Sheet 2, name the place where you want to enter the name "Inputcell".  Set the validation on inputcell  to "list" with a reference of

=wholenames

Then, instead of typing in the last name, you can pick the actual student from a list.  In the cell where you want the email address to appear, you can put the formula:

=index(emails,match(inputcell,wholenames,0))

That will put the email for the selected student in your email cell.


You might want to sort source the data on the wholenames range to make it easier to read the picklist, but that's optional.

Author

Commented:
Hi, thanks for all the advice, you guys really are great, and big thanks to Dreamboat who sloved my problem the easiest way, but still thanks to all of you!!
Anne TroyEast Coast Manager

Commented:
:D

Thanks, TDK!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial