Microsoft Access VLOOKUP from an excel fiile?? PLEASE READ?

Question

Okay, i'm moving my database over from excel to access and I need to do this:

I have an access database with lets make it simple and say two columns, Record # and First Name.

Record # is a unique number that i have manually assigned and will never be the same.

Now what I do is run a report from this access database, and it spits out the data i want. Great. Now I am going to take that data and play with it a little bit in excel and next thing i know i have an excel sheet with two columns: Record # and Code

So now i have two things, i have an access database with all my record #'s and first names, and an excel sheet with SOME record #'s and a code.

I want access to do sort of a VLOOKUP or pretty much take the code from the excel sheet and match it up with every record in the access database and put it in a seperate column called CODE1.

Answer 1

File.. Get External Data.. Link Tables .. browse to the xls file would set up a permanent link to the excel data and give the spreadsheet a kind of table name. Then it can be queried. You want to use several columns over time and you can use an 'Append query' to append data to a table; but experiment with a crosstab query. In that way, your Access table would always have 2 fields plus maybe a ColumnNumber field where you could have a default value of 1 the first week, 2 the next week, etc.. A crosstab query would present that data as several several columns (one for each ColumnNumber value). When your Access skills increase, you might be very glad that you used a small number of fields because it makes the querying of the data simpler to maintain, in my opinion.

As for VLOOKUP, you can research "query table joins" and see how a query in design view lets you choose the field which 2 tables have in common. One table is really the excel spreadsheet presented as if it is a table; the other table is the real access table.

Answer 2

Place the access data in an excel spreadsheet form. Add a column to the spreadsheet with the additional info you require. Save it. Add the field to access. Open the excel spreadsheet as a database. Presto! You could use a Vlookup but if you are using access and only have 2-3 columns of data, then I would just avoid that route. Assuming you have less than 60,000 records.

Answer 3

You're on the right track after, simply



1. import the modified Excel sheet back into access (into a new table)

2. create a modify query that adds a new column (named CODE1) to the original table which will only populate a value from columns matched in your imported table from step #1.



If you will be doing this often, you can setup import templates to make life easier.

Answer 4

This is one of those interesting problems that can also be solved with something deceptively simple called an Array Formula. Just using a regular VLOOKUP or COUNTIF won't work. Let's assume your fruit names are in column A and the numbers are in column B. Enter the following formula: =SUM(IF(ISERROR( SEARCH("Bananas", A1:A5)), "", B1:B5)) *IMPORTANT* When you enter the formula, enter it not by pressing the ENTER button, but by pressing CTRL-SHIFT-ENTER. (In other words press and hold down control, shift, and then hit enter.) You will see that curly brackets are inserted around the formula distinguishing it as an Array formula. This will return 160, which is your desired sum. SUMIF with "*banana*" that Makiavel mentions will work well too. You should select his answer.