1
I am trying to create a database. I have 2 columns, if I enter a number in the first column, the matching name should appear in the second column automatically. The list of numbers and names is on a different sheet.
How do I link these so Excel will bring up the right name when you type in a number?
ben
Posted 2013-10-02T14:36:08.673
Reputation: 13
1
As others have mentioned in the comments, this is a simple use case for VLOOKUP. Here's an example. I used a sheet titled "Lookups" to hold the list of numbers/parts, and "Sheet1" to hold the list that references the table. Lookups is a simple sheet that just holds the data, without any formulas:
Sheet is also simple, and contains a simple VLOOKUP call:
The formula (=VLOOKUP(A2, Lookups!$A$2:$B$6, 2, FALSE)) is straightforward. In the table defined in Lookups!$A$2:$B$6 (which excludes the column headers, although this isn't strictly necessary), VLOOKUP finds the row whose first element matches the value in A2, and returns the value in the column specified by the third argument, which in this case is the column containing the part name. The boolean argument FALSE tells Excel to perform an exact match, so if you enter data in column A that isn't found in the table, e.g. "10", as I've done, Excel will return #N/A instead of the last value in the table.
This exact formula does what you're looking for. The error you mentioned in the comment sounds like you may have some data validation problems or an older version of Excel, but without more details, I can't address that part of your question.
John Bensin
Posted 2013-10-02T14:36:08.673
Reputation: 1 355
that worked great but now for some reason it "ungrouped" my rows? like i go to sort them and only that column would sort. do you know how i can regroup a row, so that i can sort by any of my headings?? thank you – ben – 2013-10-03T12:27:40.633
@ben That's a separate question, so you should ask a new question about it. Also, be very specific about how your Excel worksheet is set up. What rows do you have grouped, what version of Excel are you using, what data validation rules do you have in place, etc? All of those details should be included in your new question (not in the comments here). – John Bensin – 2013-10-03T13:24:00.980
I think that the VLOOKUP here should have a 4th argument of FALSE like this - =VLOOKUP(A2,Lookups!$A$2:$B$6,2,FALSE) - if you don't include FALSE then you can only guarantee that the formula will work when the lookup range (Lookups!A2:A6) is sorted ascending - even then you can get some incorrect matches, e.g. what will happen if A2 = 10, a number that doesn't exist in the lookup range? – barry houdini – 2013-10-03T13:43:00.673
@barryhoudini Good point. I updated the answer. – John Bensin – 2013-10-03T13:57:44.227
@ben Do you have formulas set to automatically update? Are the cells in the columns of your lookup table formatted the same way as the cells in the sheet with the formula? Also, you shouldn't need quotes around the sheet name. Once again, more details are necessary. As several people have asked before, what version of Excel are you using? – John Bensin – 2013-10-03T14:26:29.300
Thank you all so much for your help! This site is great and i will be back! – ben – 2013-10-03T14:31:52.770
@ben If my answer was helpful, consider accepting it by clicking the green checkmark next to it. Also, when asking other questions, you need to specify which version of Excel you're using, e.g. 2007, 2010, etc. You'll get better, more specific answers this way. – John Bensin – 2013-10-03T14:32:59.880
So you have a table that has the name and number in it. Then you want to make a table with the name and number in it. Does this seem a little redundant to you? – EBGreen – 2013-10-02T15:07:09.583
Aaah...so you want to do this in an excel worksheet not a DB table. That is a different question altogether. – EBGreen – 2013-10-02T15:25:18.103
i want to be able to enter the number and it automatic pull up the name of whatever part# i put in. there maybe multiple jobs with the same part, but they have different info attached. so i made a list of numbers and associated names on a different sheet. is there a way to attach them? – ben – 2013-10-02T16:08:11.690
What do you mean by "database"? Just a new table in your workbook? Or a relational database like Access or MySQL? Please provide more detail because, as is, it's really unclear what you're trying to do. – Excellll – 2013-10-02T16:24:44.393
sorry, i made a table in excel that i can add items to for my job. on a separate sheet i have a list of associated part # and part Names. on the first sheet i enter the job #, then the part #, then i want excel to automatically pull the part name from the other sheet. is that possible? – ben – 2013-10-02T16:31:13.137
will the VLOOKUP function work for you, or do you want to use VBA? – datatoo – 2013-10-02T16:40:33.960
i dont know what either of those are but iwill see if they do it and get back to you thank you – ben – 2013-10-02T16:52:18.310
ok VLOOKUP is great except every time i try to input the formula, i get an error message, "you may not use references to other worksheets or workbooks for calculated column criteria" anyone know how to get around this? thank you so much! – ben – 2013-10-02T17:49:34.723
@ben What version of Excel are you using? You may be seeing this error if you're using a version of Excel before 2010.
– John Bensin – 2013-10-02T20:52:40.727