how do i create a lookup table in microsoft excel
130312
A simple two column lookup table with one column with values to match and another column with values to return. Two columns A & B:\n\n7 Bike\n8 Car\n9 Boat\n\nOn another sheet or workbook you could use the vlookup function to find in col A, a number and return the value in the second column.\n\nTo find car the formula would be =vlookup(8,A1:B3,2,False)
133832
If for example your older data is on Sheet1, then in the column next to the 'New company name type this formula in cell B1\n\nHere is the formula\n\n=IF(ISNA(VLOOKUP (A1,Sheet1!$A$1:$A$2310,1)), "New Data","Existing Data")\n\nCopy this formula next to each of the new addresses.\n\nIf it finds an existing entry the Word "Existing Data" will appear, and if it's a brand new entry then the word "New Data" will appear!\n\nYou can then simply use an AutoFilter to sort out the new and existing data (You will need headings over your data)\n\nHope this helps.
363083
I know this is going to sound a bit backwards and verbose, but you really need to setup the tables a bit differently. What you are in need of is what is called a "conjunction table" which you can then query to give you this exact information. This is how it works...\n\nYou have a table called "rooms" which lists all the room numbers for all types of rooms. Your room numbers are your primary key by the way. In this table you could also put fields like how big the room is, extra features, what services it has etc.\n\nYou then need a tabled called "type_of_rooms" where you have the fields typeid, typename. Here you list each type of room you have. A single, double, a suite, presidential sweet, jungle room etc. This table could also hold your base pricing for that type or room etc.\n\nNow the third table is called "rooms_types" and contains two fields which are both keys. One goes to the rooms table (room number field) and the other goes to the type_of_room table (to the typeid field).\n\nSo what does this all get us? Well... this new rooms_types of table is going to allow you to do many things. First it allows you to associate multiple room types with a single room. So lets say you have the following data in the "type_of_rooms" table...\n\ntypeid, typename\n\n1, single\n2, double\n3, suite\n4, presidental\n5, executive\n\nThen you have the following data in the rooms table...\n\nroomnumber, base_price\n\n100, $20.00\n122, $40.00\n111, $60.00\n\nNow your new table "room_types" can contain data like...\n\nroomnumid, roomtypeid\n\n100, 1\n101, 1\n123, 2\n111, 3\n111, 4\n\nThis data above shows that room number 111 is a suite, but is all a presidential room (or a presidential suite). Understand? Ok, so now to the query you need to understand this. I want to get all rooms which are singles...\n\nSelect roomnumberid from rooms_types where roomtypeid = 1\n\nThe records return are "100 and 101". So how you then display it on the page or program is completely up to you. \n\nThe advantage is to all this is flexibility. So lets say you decided to upgrade room 100 from a single to a new suite. You can go to the  new "rooms_types" table and change number 100 to have a roomtypeid of 3 and thats it!\n\nWe run the query again and it will return the results room number 101. Lets say you want to change the base price for all single rooms, you go to the rooms table and change the base price to $35.00 and suddenly all your single rooms are now changed. \n\nI know this is a bit longer than you had planned, but I hope you followed me here. This solves your immediate formula problem plus gives you the ability to change values quickly and easily and also help keep your database size down. It is a method called "database normalization".\n\nGood luck with this project!
605107
It seems that you may have a sum function built into this thing--you know how many times the code GHI occurs, for example.  Maybe you manually counted them, but I suspect they are tabulated for you in some way.\n\nSo build a table.  The first column would list any possible code for a given day.  The second column would list any possible code for the following day.  The third column lists how many times that occurs.\n\nFor example, suppose that the only possibilities are A, B, and C.  I might have the following table:\nA A 5\nA B 3\nA C 4\nB A 7\nB C 2\nC A 1\nNotice that this means that an "A" day has been followed by an "A" day 5 times, by a "B" day 3 times, etc.  A "B" day has never been followed by a "B" day.\n\nOnce this table is created, simply sort it, using Column 1 as the first sort key, column 3 as the second sort key (putting the most likely successor at the top of the list), and column 2 as the third sort key.
318937
next to insert table button on the standard toolbar you have insert microsoft excel sheet button. click and drag the number of rows and columns you need.
616009
This is not a simple question to easily be answered.  First, the 'data' must be established in Excel.  Obtain the different fields of information and place each into it's own column.  Sep-10, Chicago, GreenBay.  Place the data beginning down a ways like row 15, and column D.  Label each column with a heading and keep the label simple: Begin with a letter, no more than 8 characters, no blanks, no spaces, meaningful to content, and not a reserved word (Excel command like SUM).  Then highlight the entire data range you placed and choose the INSERT-NAME-DEFINE and type database.  Then you can Go to the Heading line and highlight that line and copy to row 2 immediately above and paste it and also below the bottom line and leave 10-15 empty lines and paste the heading line again.  The top area should be INSERT-NAME-DEFINE and type Criteria.  The bottom area should be INSERT-NAME-DEFINE and type Extract.  When you define the Criteria you should include one blank line immediately below that heading line where you can place criteria that you would like to extract to the extract area from your 'Database".  The Extract area is just the data on the copied header line.  You place information like "Giants" under the FIRSTTEAM column and then on the Excel menu you select DATA-FILTER-ADVANCE FILTER-COPY TO ANOTHER LOCATION - UNIQUE RECORDS ONLY - OK and all the 'Giants' information will be copied to the extract area.  You can UNDO a selection by DATA-FILTER-SHOW ALL.  \n\nThis is the Database feature within Excel and is simple to setup and very useful to extract many types of data.
245581
I use MS Excel.  I use each worksheet in a workbook for the different months of a calendar year.  I copy the previous month's rows into the current months and erase the amounts due, dues dates, etc, and put in the new ones, then add new rows as required.  I use a gray font color for rows that don't apply, and use a red font for unpaid items.  As I pay them, I convert them to green.  You can use summary cells to add stuff up.
457924
MS Excel has tons of great formulas.  Have you thought about using that.  Also, not sure what sort of database application you're using but formulas can usually be programmed into them.
9997
I'd copy the emails from each sheet & do a vlookup to each set. Delete the ones that are matched & then do a vlookup to the original sheets to bring ini the rest of the details.\nI'd take them into Access tho if you can. You could link the sheets to a data base & export the query results back to Excel.
257563
There are a lot of software libraries that do that:\n\nhttp://tinyurl.com/k9zah\n\nincluding some for Excel:\n\nhttp://tinyurl.com/hrda7
342523
1) In a Word table, you can insert a formula.  Try it with a column of numbers.  Move cursor to the last cell, click TABLE, then choose FORMULA.  Choose SUM(Above).  This will add all the cell values.  The formula will look like this: {=SUM(ABOVE)}\n\n2) Using same WORD table, copy the numbers to a different column and try inserting a new formula.  This time choose a formula from the formula list.  I recommend AVERAGE.  Then the formula looks like this {AVERAGE(BELOW)}.\n\nTo view the formulae described above, highlight the result cell, right click, choose toggle field codes.  Repeat to return it to result.\n\nFor auto-text:\n\n1) Click INSERT, choose, AutoText, then choose which you want to use.  I presume that page numbering is probably most commonly used.  Best to insert it in header or footer.\n\n2) Try a reference.  Click on INSERT, choose Cross-Reference.  In order to use this feature you need to have some styles in use that re numbered.  They will appear in the cross-reference box for you to choose from.\n\n\nHope these help,\n\nKind Regards,\nQwertyKPH @ Yahoo!
541200
EXCELL - Spreadsheets
95378
I've uploaded a simple spreadsheet to the link below.  Anything else and you should probably find someone in the office who knows Excel.
306810
MS Access environment\n\nYou must create a field in Table Teachers that uniquely identifies each teacher. It can be an Autonumber field.\nGiven this, each time you add a teacher, the software will give this record a unique number.\n\nIn Students Table, create a field with number Long Integer data type.\n\nGo to Tools>Relationships and set a one-to-many relationship between the 2 tables using these 2 fields.\n\nCreate a query with this 2 tables using the fields in table Students and only the fields in table Teachers you want to show (not the autonumber field)\n\nMakiavel
458923
Microsoft offers a free personal budget template for Excel:\n\nhttp://office.microsoft.com/en-us/excel/HA010450871033.aspx
673699
The exact works, but can be cumbersome.\n\nThe best method is vlookup.  It is designed for exactly what you want to do.  However, there are 2 things  you need to do first.\n\n1) get rid of unwanted spaces before or after the last name.  To do this, use this formula in column C =trim(b1).  This trims the spaces off and puts the remainder in column c.  Repeat for the data in column F and put the result in Column G\n\nNow copy the data in C and Paste Special if back into B.  Repeat, moving data from G to F.  Delete the data in columns C & G.\n\n2) You have to sort the data in column B (your lookup column).\n\nNow you have all that done, the vlookup.\n\nin your now empty column F, do this:\n\n=VLOOKUP(G1,$b$1:$b999,1,FALSE)\n\nWhat this does is lookup what is in G1 and searches the range b1 through b999 (change this as needed), it will return the data in the first column (you can get back another column's data if you wish - you have to use multiple columns after the first ,) and it looks for an exact match (this is the "FALSE")\n\nRepeat for each row.
629951
You cannot do that in Windows folders. You may have to use some other software for this purpose. One way is to create an excel spreadsheet with following columns:\n\nFirstName MiddleName LastName Folder\n\nNow put the link to the folder in the Folder column so that you can click and open the folder. You can sort by first name or last name using Data -> Sort menu