-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
"VLOOKUP" in Access
- Thread starter DeniseH
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,171
- Office Version
- 365
- Platform
- Windows
WELCOME TO THE BOARD!
Many times, you don't need to. Access is a relational database program, so many times all you need to do is link two tables together, and pull out the fields you need.
If you do not think this is what you want or if you want specific help, please describe exactly what you are trying to do in detail, and provide some examples.
Many times, you don't need to. Access is a relational database program, so many times all you need to do is link two tables together, and pull out the fields you need.
If you do not think this is what you want or if you want specific help, please describe exactly what you are trying to do in detail, and provide some examples.
Upvote
0
Thanks for the quick reply! But no, my problem is a bit more detailed than that. In particular I am wanting to take a value, listed by catalog #, look up that value in a table and return another value in either the 2nd, 3rd, or 4th column, depending on one of three physical locations (each catalog # can have up to 3 locations, although the look-up value will be the same for all three). In addition, there is another field connected to the catalog # that shows what source each catalog has. The source is the decision maker between two similar tables. I can put all of these in one table, but I have no idea how to pull out the value for each catalog. One more thing, these will not be an exact match, but the values are in ascending order in the lookup table. In Excel, the formula would be something along the line of an IF statement with 2 VLOOKUPs nested in it.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,171
- Office Version
- 365
- Platform
- Windows
You still may be able to do this in a query linking the two tables, but the details you provided are still a bit murky.
If you could actually provide a few simple example, listing the some data you have in each table, and show the output you are expecting, it would go a long way in helping us to give you an answer that will work for you.
If you could actually provide a few simple example, listing the some data you have in each table, and show the output you are expecting, it would go a long way in helping us to give you an answer that will work for you.
Upvote
0
In Excel, the base file has 5 columns:
Catalog Source Loc1 Loc2 Value
123456 China 422
673334 Brazil 1827
etc.
Loc1 and Loc2 are what I'm using the Vlookup to find.
"China" lookup table is as follows:
Amt Loc1 Loc2
0 7.9 8.25
301 6.7 7.12
1001 5.2 6.1
"Brazil" lookup table is similar, but with different values:
Amt Loc1 Loc2
0 5.25 5.89
501 4.23 5.07
1001 3.71 4.33
3001 2.25 3.04
In the 1st example, the Loc1 and Loc2 values returned would be 5.2 and 6.1 respectively (looking up the 422 units in the "China" table). In the 2nd case, the Loc1 and Loc2 values returned would be 3.71 and 4.33, respectively.
I had thought about putting together a table or crosstab query that would show all possibilities for each item, then somehow choosing from that, but I'm not sure that's the best way. Something so easy in Excel should "translate" to Access without this much problem, shouldn't it?
Catalog Source Loc1 Loc2 Value
123456 China 422
673334 Brazil 1827
etc.
Loc1 and Loc2 are what I'm using the Vlookup to find.
"China" lookup table is as follows:
Amt Loc1 Loc2
0 7.9 8.25
301 6.7 7.12
1001 5.2 6.1
"Brazil" lookup table is similar, but with different values:
Amt Loc1 Loc2
0 5.25 5.89
501 4.23 5.07
1001 3.71 4.33
3001 2.25 3.04
In the 1st example, the Loc1 and Loc2 values returned would be 5.2 and 6.1 respectively (looking up the 422 units in the "China" table). In the 2nd case, the Loc1 and Loc2 values returned would be 3.71 and 4.33, respectively.
I had thought about putting together a table or crosstab query that would show all possibilities for each item, then somehow choosing from that, but I'm not sure that's the best way. Something so easy in Excel should "translate" to Access without this much problem, shouldn't it?
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,171
- Office Version
- 365
- Platform
- Windows
Even in Excel, in order to use VLOOKUP, you have to match on a field. But I do not see any linking field between your Base Table and your Lookup Tables.
What exactly does your Excel VLOOKUP formula look like?
What exactly does your Excel VLOOKUP formula look like?
Upvote
0
=IF($C5="S",VLOOKUP($J5,Domestic,2,TRUE),VLOOKUP($J5,Offshore,2,TRUE))
Where C5 is the source (1-letter code in this case), J5 is the Qty, "Domestic" and "Offshore" are the two table names (range names in Excel) - the "TRUE" at the end indicates that this in NOT an exact match, but a range lookup - i.e., it's taking the value and looking it up on the table to see which range it falls in (422 is >301 but <1001). The ",2," tells is which column to use when choosing the return value. I noticed that my examples didn't post quite the way I had typed them - it's hard to read, but Loc1 and Loc2 are blank in both cases. The 422 and (I can't remember the other #) are the lookup values.
This works just fine in Excel - trying to move it to Access because it's part of a larger process that we would like to run overnight with no manual intervention.
Where C5 is the source (1-letter code in this case), J5 is the Qty, "Domestic" and "Offshore" are the two table names (range names in Excel) - the "TRUE" at the end indicates that this in NOT an exact match, but a range lookup - i.e., it's taking the value and looking it up on the table to see which range it falls in (422 is >301 but <1001). The ",2," tells is which column to use when choosing the return value. I noticed that my examples didn't post quite the way I had typed them - it's hard to read, but Loc1 and Loc2 are blank in both cases. The 422 and (I can't remember the other #) are the lookup values.
This works just fine in Excel - trying to move it to Access because it's part of a larger process that we would like to run overnight with no manual intervention.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 72,171
- Office Version
- 365
- Platform
- Windows
I am quite familiar with Excel and VLOOKUP (I made my living programming in Excel long before I got involved in Access).
I am still a little perplexed by your Excel setup. So do you have a separate lookup table for each and every "Source" (China, Brazil, etc). It would probably work much better in Access if you had these "Sources" as one large table, and have in this table named "Source" that has these values (China, Brazil, etc). This would create a linking field you could use to connect your tables.
To best utilize Access, it is important to understand how Access works. It is a relational database tool, which means you have various tables which are "connected" or "linked" by common field(s). If there is nothing in common to link them on, I don't think you will gain any advantage to doing it Access versus Excel. Also, Data Normalization is also an important factor that can come into play with Access. If your tables are not designed properly, you are probably going to create a lot of frustration for yourself. My first dabbling in Access was to create a billing database, and I spent months on it, only to have to go back and educate myself on Access and re-start it all over again.
It may be best to take a step back here. If you already have something in Excel that works, but is not quite automated to the way you like, and you really aren't proficient in Access, you might be best to keep it in Excel and look for help with the automating piece.
What is it that you would like it to do in Excel that it does not do now?
I am still a little perplexed by your Excel setup. So do you have a separate lookup table for each and every "Source" (China, Brazil, etc). It would probably work much better in Access if you had these "Sources" as one large table, and have in this table named "Source" that has these values (China, Brazil, etc). This would create a linking field you could use to connect your tables.
To best utilize Access, it is important to understand how Access works. It is a relational database tool, which means you have various tables which are "connected" or "linked" by common field(s). If there is nothing in common to link them on, I don't think you will gain any advantage to doing it Access versus Excel. Also, Data Normalization is also an important factor that can come into play with Access. If your tables are not designed properly, you are probably going to create a lot of frustration for yourself. My first dabbling in Access was to create a billing database, and I spent months on it, only to have to go back and educate myself on Access and re-start it all over again.
It may be best to take a step back here. If you already have something in Excel that works, but is not quite automated to the way you like, and you really aren't proficient in Access, you might be best to keep it in Excel and look for help with the automating piece.
What is it that you would like it to do in Excel that it does not do now?
Upvote
0
Similar threads
- Question
