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
Medium Priority
233 Views
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
Question by:dddw
• 4
• 2

Expert Comment

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.

test.xlsx
0

Author Comment

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

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

Author Comment

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

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

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

Author Closing Comment

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

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.
###### Suggested Courses
Course of the Month13 days, 3 hours left to enroll