Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel look up formula

Posted on 2014-07-14
7
Medium Priority
?
233 Views
Last Modified: 2014-07-15
Hi Experts,

Please see the attached sample spreadsheet.  I need a formula on sheet1 column D that will look up values in sheet2 column C- highlighted in yellow.

The spreadsheet has thousands of records so that is why I'm asking for help to make this task easier.

Thanks,
David
test.xlsx
0
Comment
Question by:dddw
  • 4
  • 2
7 Comments
 

Expert Comment

by:Shark_
ID: 40195766
=VLOOKUP(Sheet1!A2,Sheet2!A1:C11,3)

is easier if you sort both sheets by the first column. you need to use a better identifier than just the fist name because you can get duplicates and the result will be the first name it founds.

i uploaded your files back with the formula working
test.xlsx
0
 

Author Comment

by:dddw
ID: 40195773
I have some limitations with the data such as not being able to re-sort it.  Is there away to use the VLOOKUP based on the first and last name?
0
 

Expert Comment

by:Shark_
ID: 40195778
I think no, it just looks an specific value in  a mark table. you will have to create an other field adding the first name and last name in both sheets. but if you are not able to sort then I don't think you will be able to add a column. can you copy and paste the data in to an other excel book that is not restricted?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dddw
ID: 40195800
I'll give the VLOOKUP a try.  It may work.  There is a unique identifier that could be used instead of the name.  I'll let you know how it goes.
0
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 40195802
Use this formula in D2 to lookup first and last name, copy down.
=INDEX(Sheet2!$C$2:$C$15,MATCH(A2&B2,Sheet2!$A$2:$A$15&Sheet2!$B$2:$B$15,0),1)
The formula must be array entered Ctrl+Shift+Enter.
Excel will enclose the formula in {} to show that it is an array formula.
Expand the ranges to what you need, don't use the entire columns on array formulas.
0
 

Author Comment

by:dddw
ID: 40195810
That works very well.  I'll give that a try on the actual data.
0
 

Author Closing Comment

by:dddw
ID: 40198096
I ended up using this formula for the finalize spreadsheet with some modifications to the columns it looked at.

Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question