How to convert the vlookup function from excel to access

See the attachments
lookup.xlsx
Jone ElliaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Depending on how your database tables are laid out, you should be able to use the DLOOKUP() function in Access to do similar things.  Further info below...



»bp
0
Jone ElliaAuthor Commented:
thanks a lot Bill Prew
but how can i create a same function in the access?
0
Bill PrewIT / Software Engineering ConsultantCommented:
In the example Excel file, it looks like you are doing a matching process.  That can be done in Access using an Outer Join.  Attaching a sample Access database with your two main columns of data as tables tblInput, tblLookup), and then created a Select Query (qryMatch)  that joins the two tables matching on the number column.  When you run the query you will see the results similar to your middle column in Excel.

Without knowing any more about what you are trying to do this was what I came up with, hope it gives you the idea of what's possible.

EE29077570.accdb


»bp
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Jone,

Glad you made it here. Welcome to Experts Exchange.

I made a video on "converting" vLookup in Excel to Access, which I believe you saw.  The point of that video is that often there is no need to "convert" those formulas since, in Access, you can use a query and link the tables. For others reading, here that video:
https://www.youtube.com/watch?v=ED_Wd4w9PLk

The first step would be to create the tables in Access.  Then make a query, join the tables, and get what you want out of each one.  I did not download the example Bill gave you, but I assume that is what he did for you.

When you post a question, be sure to describe what you need and where information to look at is located.

have an awesome day,
crystal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jone ElliaAuthor Commented:
many many thanks ,Bill Prew and cristal
it was very useful
0
Bill PrewIT / Software Engineering ConsultantCommented:
Glad you got something useful.  For future reference, you can select "assisted" comments as well as a single "solution" when you close a question.  Perhaps you know that, and felt only one comment here was helpful, which is fine, but wanted to make sure you knew that you can recognize more than one useful comment when it's appropriate to you.

Take a look at this article, it should help you with the question close process.


For future reference here some other items related to closing questions in case they are helpful.


And don't forget you can always "request attention" on a question if you are not sure how to proceed and a support person will be in touch to help you out.



»bp
1
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Jone ~ happy to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.