Solved

# Excel Lookups

Posted on 2012-09-04
295 Views
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

Pete
0
Question by:PeterHing

LVL 19

Assisted Solution

In cell F1 of sheet1 use this formula...

=VLOOKUP(E1,Sheet2!A1:B5000,2)

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.
0

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
0

LVL 10

Accepted Solution

If the list in column A is not sorted, you will have to change the formula to:
=VLOOKUP(E1,Sheet2!A1:B5000,2,False)
0

LVL 19

Expert Comment

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.
0

## Featured Post

### Suggested Solutions

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.