# Can I do this with Excel? **URL:** \ **Category:** In My Humble Opinion\ **Created:** [July 23, 2014, 8:42pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875 "2014-07-23T20:42:17Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 23, 2014, 8:42pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/1 "2014-07-23T20:42:17Z")
I’ve got two spreadsheets. One is just a list of names. The other is a bigger list of names, including all the names in the first spreadsheet (but with additional names as well), with a numerical value in the next column over–one value for each name. I would like to create a third spreadsheet which contains a column of just the smaller list of names, but with (in the next column over) the numerical value for that name from the second spreadsheet. So for example, if my first spreadsheet is just: John Doe Jane Moe And my second spreadsheet is: John Doe | 5 Jim Roe | 3 Jane Moe | 9 I would like the final spreadsheet to be: John Doe | 5 Jane Moe | 9 Of course the actual case I’m talking about involves much longer lists, such that doing it by hand would be fairly arduous. ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [July 23, 2014, 8:49pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/2 "2014-07-23T20:49:47Z")
Sure. Copy and paste your first page list to the third page. Now you have all the names. Then do a V-Lookup for the names against the list on the 2nd page. Your numbers will then be on the third page next to the name. [=vlookup(Sheet1!A:A,Sheet2!A:B,2,FALSE)] StG ---
**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\ **Post date:** [July 23, 2014, 10:32pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/3 "2014-07-23T22:32:22Z")
> [@StGermain](#): > > Sure. Copy and paste your first page list to the third page. Now you have all the names. Then do a V-Lookup for the names against the list on the 2nd page. Your numbers will then be on the third page next to the name. > > [=vlookup(Sheet1!A:A,Sheet2!A:B,2,FALSE)] > > StG I’ve always had it look up the specific cell value – is that not necessary? It looks for the first “a” in Sheet 2 column A and returns what is in the second column: a 1 [+VLOOKUP(A1,Sheet2!A:B,2,FALSE)] s 2 [+VLOOKUP(A2,Sheet2!A:B,2,FALSE)] b 3 [+VLOOKUP(A3,Sheet2!A:B,2,FALSE)] d 4 etc. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [July 24, 2014, 12:12pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/4 "2014-07-24T12:12:35Z")
Just remember if you use vlookup you need to sort the names on the second page in ascending order. ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [July 24, 2014, 1:22pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/5 "2014-07-24T13:22:20Z")
> [@gigi](#): > > I’ve always had it look up the specific cell value – is that not necessary? It looks for the first “a” in Sheet 2 column A and returns what is in the second column: > > a 1 [+VLOOKUP(A1,Sheet2!A:B,2,FALSE)] > s 2 [+VLOOKUP(A2,Sheet2!A:B,2,FALSE)] > b 3 [+VLOOKUP(A3,Sheet2!A:B,2,FALSE)] > d 4 etc. No, you’re right. The lookup value is usually a single cell. I was writing the formula from memory and not looking at excel. **jonesj2205** - You don’t have to have the cells in alphabetical or numerical order if you’re using absolute values in the array field. It looks at all those same fields for each lookup value. So [=vlookup(a1,Sheet2!$A:$B,2,False)] will look at the whole columns A and B for each lookup value. StG ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [July 24, 2014, 1:43pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/6 "2014-07-24T13:43:47Z")
> [@StGermain](#): > > **jonesj2205** - You don’t have to have the cells in alphabetical or numerical order if you’re using absolute values in the array field. It looks at all those same fields for each lookup value. So [=vlookup(a1,Sheet2!$A:$B,2,False)] will look at the whole columns A and B for each lookup value. Well I’ll just have to go try that, thanks. I’ve been using Index, Match more because of that. ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 24, 2014, 4:57pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/7 "2014-07-24T16:57:23Z")
Okay, generalizing the problem: Can I do something similar, but with more than one column of values? I can see how to do it with a separate vlookup in each cell in question. I’m hoping for something more streamlined if it exists. It appears what I’m ultimately after is something like what gets done by JOIN in SQL, if that helps. (I say that being mostly ignorant about SQL, it’s just a connection that came up as I was googling.) Say, you can’t use SQL to make two excel spreadsheets talk to each other, can you? Just in excel itself? (wishful thinking, crossing fingers) ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [July 24, 2014, 5:26pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/8 "2014-07-24T17:26:22Z")
> [@Frylock](#): > > It appears what I’m ultimately after is something like what gets done by JOIN in SQL, if that helps. > > (I say that being mostly ignorant about SQL, it’s just a connection that came up as I was googling.) > > Say, you can’t use SQL to make two excel spreadsheets talk to each other, can you? Just in excel itself? (wishful thinking, crossing fingers) Keep in mind that SQL is a language developed to control/modify databases. While a multiplicity of Excel spreadsheets can weakly mimic an actual database, Excel is not a database program. The response **St. Germain** gave in post #2, the VLOOKUP function, is how Excel mimics the JOINs and WHERE clauses of SQL. To bring in more than one column of values, you need more than one VLOOKUP function – one for each column you bring in. Taking **St. Germain’s** sample formula as a starting point, the data set selected from (the “array” is one term for it) needs to be defined with more columns, and the number after it needs to change. You’d end up with multiple VLOOKUP functions like so (assuming the larger data set has a header row and 300 values, and that the smaller data set has four fields you want populated in Row 2): [in Sheet1!B2]**=vlookup(Sheet1!A2,Sheet2!$A$1:$E$301,2,FALSE)** [in Sheet1!C2]**=vlookup(Sheet1!A2,Sheet2!$A$1:$E$301,3,FALSE)** [in Sheet1!D2]**=vlookup(Sheet1!A2,Sheet2!$A$1:$E$301,4,FALSE)** [in Sheet1!E2]**=vlookup(Sheet1!A2,Sheet2!$A$1:$E$301,5,FALSE)** _(only the blue text above represents the actual formulas. The dollar signs in the array designation keep the array from changing should you copy these VLOOKUP formulas down into other rows)_ You’re starting to bump against some of the reasons spreadsheet-based data processing gets ditched in favor a full-blown database. Databases can do a lot of this kind of analysis more cleanly and with less user intervention. Yes, there are offices all over that have a resident Excel guru who’s built impressive edifices of formulas, macros, and Visual Basic scripts. Those edifices get unwieldy quickly, however, and are prone to corruption and to difficult-to-trace failures. ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 24, 2014, 6:32pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/9 "2014-07-24T18:32:04Z")
Thanks for that information. One of the causes of the problem(s) I’m running into is that the only way I can interface with any of this information is through excel spreadsheets automatically generated from a database management system we use here. I have no control over what reports are available, I have to take what I can get, and then use Excel to piece together what I want. They’re strapped for time and money, and apparently no one here knows SQL anyway (the one who did is gone). As far as I can gather, SQL is what would be needed to create new types of reports. I suggested maybe I could learn the basics and go in and do the things I need myself, but they laughed me out of the room. (Literally, loudly.) ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 24, 2014, 6:36pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/10 "2014-07-24T18:36:05Z")
BTW I tried what presently looks to me like exactly what you suggest and got a bunch of errors ("#N/A") in every cell I put the formula in. But the similarity of what I was trying to what you just typed up makes me think I should keep working in that direction, so that’s good. Just a bug, rather than a completely wrong approach. ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [July 24, 2014, 7:13pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/11 "2014-07-24T19:13:58Z")
> [@Frylock](#): > > BTW I tried what presently looks to me like exactly what you suggest and got a bunch of errors (“#N/A”) in every cell I put the formula in. But the similarity of what I was trying to what you just typed up makes me think I should keep working in that direction, so that’s good. Just a bug, rather than a completely wrong approach. **Frylock** , I don’t know your level of expertise, so this advice could be helpful, or it could be “_please – who DOESN’T know that?_”: Those formulas I wrote in blue text above … those are only samples. The exact formula that will work in your spreadsheet will depend on how the data is situated in your actual Excel spreadsheet/tabs. Sheet names like “Sheet1” and “Sheet2” are only defaults – they could be named anything else in _your_ file. The specific columns (A, B, C, etc.) in _your_ data might not be A through E. And so on. An additional note about matching names across two Excel tabs/spreadsheets: they must match EXACTLY. Any differences at all will result in a “no match”. Common causes of “no matches” are commas, extra space character before/after a name, misspellings, change in FirstName/LastName order, etc. ---
**Author:** ![bordelond](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bordelond/32/150_2.png) [@bordelond](https://boards.straightdope.com/u/bordelond)\ **Post date:** [July 24, 2014, 7:21pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/12 "2014-07-24T19:21:45Z")
> [@Frylock](#): > > I suggested maybe I could learn the basics and go in and do the things I need myself, but they laughed me out of the room. (Literally, loudly.) Who’s “they”? A lot of database admins jealously guard their data, and think access to non-experts is “dangerous”. IME, this danger is rarely present when novices aim to _view_ (as opposed to _modify_) data. SQL, for the purposes of pulling data out of a database, does not have much of a learning curve at all. Better yet, there are many third-party software options that will build & execute the SQL statements for you through a drag-&-click interface (if you’ve heard of Crystal Reports, that’s a popular one). There’s no real-world reason for your database folks to be obstructionist about it, unless they are trying to avoid not much extra work on their end. Or else they have a poor understanding of how to erve their data “customers”. BTW, it’s well-known that unchanging canned reports are a lousy way to deal with data. In most environments, data needs change with some frequency. You’re not pulling the same kind of data for years on end without changes in expectations, business rules, etc. An inflexible database administrator is a bad database administrator. ---
**Author:** ![StGermain](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/stgermain/32/2868_2.png) [@StGermain](https://boards.straightdope.com/u/StGermain)\ **Post date:** [July 24, 2014, 7:41pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/13 "2014-07-24T19:41:22Z")
Access will allow you to import Excel data into tables, which you can then tie together using SQL. But you can easily do more than 1 Vlookup. I have one report that I run where I’ve written a macro to reformat columns, run 10 Vlookups from several worksheets. All this collating takes the macro less than a minute to do. StG ---
**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\ **Post date:** [July 24, 2014, 10:53pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/14 "2014-07-24T22:53:54Z")
> [@jonesj2205](#): > > Just remember if you use vlookup you need to sort the names on the second page in ascending order. I don’t think so. It just looks for the first one it finds in the column you tell it. This does become an issue if you have the same name more than once with a different value in the second column, since the lookup will return the first one. ---
**Author:** ![Grestarian](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/grestarian/32/3227_2.png) [@Grestarian](https://boards.straightdope.com/u/Grestarian)\ **Post date:** [July 24, 2014, 11:48pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/15 "2014-07-24T23:48:47Z")
There’s a lot of great input above. I just thought I’d call out as I wander by that… 1. Excel includes ODBC (i.e. database) functions which can be explicitly invoked. 2. Teach Yourself SQL In 21 Days is a popular way to get your feet wet. You might even like building simple queries. More complex queries may take more time and effort. There are also other books, and a few on-line tutorial websites. I used a combination of all three and can do okay for some simple data-dumps (like what you’ve asked about). We have contractors who do the major financial reports, and once in a while they give me tips and lessons. 3. I think if you’re smart enough to contribute regularly to The Dope boards (and I’ve seen you around for quite a while) you’re smart enough to dabble in SQL a bit. You might end up finding another way to get your solution for the original question, but you’ll end up with another tool in your pocket with which to solve some other queries down the road. —G! ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [July 24, 2014, 11:53pm UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/16 "2014-07-24T23:53:58Z")
I would go the Access linking to spreadsheets method that StGermain suggested. ---
**Author:** ![stw004](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@stw004](https://boards.straightdope.com/u/stw004)\ **Post date:** [July 25, 2014, 2:02am UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/17 "2014-07-25T02:02:17Z")
Use Access to create a relationship. Barring that, the quickest work around is to create a simple pivot table out of the longer list, then use the “GETPIVOTTABLE” function in your smaller list to grab the values and populate your field ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 25, 2014, 2:15am UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/18 "2014-07-25T02:15:57Z")
Haha thanks for the pointers everyone. I’d never dealt with Access before but I’ve gone in and poked around a bit and I think this is going to be cake. (Overconfidence…) My evil scheme is to design an SQL query that will assemble a full spill of all available information from the bits and pieces we get through our exported Excel reports, then from there I’ll be able to do whatever I want. 😉 (This is probably naive–for all I know there’s too much there to let this be manageable, but anyway, learning experience.) ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [July 25, 2014, 2:29am UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/19 "2014-07-25T02:29:58Z")
> [@stw004](#): > > Use Access to create a relationship. > > Barring that, the quickest work around is to create a simple pivot table out of the longer list, then use the “GETPIVOTTABLE” function in your smaller list to grab the values and populate your field That term “pivot table” keeps coming up when I google around about things I’m trying to do. I will have to learn this thing. ---
**Author:** ![Isilder](https://avatars.discourse-cdn.com/v4/letter/i/cc9497/32.png) [@Isilder](https://boards.straightdope.com/u/Isilder)\ **Post date:** [July 25, 2014, 2:40am UTC](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875/20 "2014-07-25T02:40:32Z")
> [@Frylock](#): > > Haha thanks for the pointers everyone. I’d never dealt with Access before but I’ve gone in and poked around a bit and I think this is going to be cake. (Overconfidence…) > > My evil scheme is to design an SQL query that will assemble a full spill of all available information from the bits and pieces we get through our exported Excel reports, Are you sure you cannot get Access linked to the database directly - “LIVE” ? Well the DB people may not want to give you full access to the database, but they can give you a read only “view” … linked to your access … the view would be the “select” of SQL, of only the fields you need, but it can include “join”… so right there the guts of your report is already done. You ask for a report linked to your Access, they do it in like 5 minutes… [Next page](https://boards.straightdope.com/t/can-i-do-this-with-excel/693875.md?page=2)