Excel look up formula

Posted on 2014-07-14
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.

Question by:dddw

    Expert Comment


    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

    Author Comment

    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?

    Expert Comment

    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?

    Author Comment

    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.
    LVL 20

    Accepted Solution

    Use this formula in D2 to lookup first and last name, copy down.
    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.

    Author Comment

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

    Author Closing Comment

    I ended up using this formula for the finalize spreadsheet with some modifications to the columns it looked at.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
    Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
    This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
    XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now