Excel Lookups

Posted on 2012-09-04
Last Modified: 2012-09-04
Hi All,

In Sheet1 Column E - I have a list of names
In Sheet2 Column A - I have a list of names
Also in Sheet2 Column B - I have their Logon IDs

How Can I get Excel in Sheet1 Column F to look at the name in E > Find it in Sheet2 Column A > Then show their Logon ID from Column B

Many Thanks in Advance,

Question by:PeterHing
    LVL 19

    Assisted Solution

    by:Ken Butters
    In cell F1 of sheet1 use this formula...


    Then fill down for other rows on sheet1

    Note: I randomly said B5000 on sheet2.... that would assume that you have no more than 5000 rows.  Increase/decrease as appropriate for your data.
    LVL 2

    Author Comment

    Hi buttersk,

    Thanks for your reply. I ran what you suggested but it appears to be picking the wrong IDs from Sheet2 column B. I can't even see how it's getting the result - it's very random.

    To confirm,

    If "Andy User" is in Sheet1 / Col E / Cell 45
    Lookup "Andy User" in Sheet2 / Col A (no more than 2000 rows)
    Get his ID from Col B (So if found in A500, his ID is in B500)
    Write to Sheet 1 / Col F / Cell 45
    LVL 10

    Accepted Solution

    If the list in column A is not sorted, you will have to change the formula to:
    LVL 19

    Expert Comment

    by:Ken Butters
    Yes... that is right the formula should work as you are describing.

    ... I should have added the false parameter as per tdlewis.

    that should fix it for you.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now