Index Match question

[praetorianprefect]

Is there a way to replicate the Index Match function in MS Access?

[alansidman]

Perhaps it would be beneficial if you provided details of what you are trying to do. Sometimes asking a question as you have in a forum like this will get you off track and take a long time to resolve. It is better to be as specific as possible, because the answer to your question could be: Maybe.

[praetorianprefect]

Perhaps it would be beneficial if you provided details of what you are trying to do. Sometimes asking a question as you have in a forum like this will get you off track and take a long time to resolve. It is better to be as specific as possible, because the answer to your question could be: Maybe.

My apologies for assuming that those who frequent this forum are familiar with the Excel function Index-Match.

http://support.microsoft.com/kb/214142 explains the Index Match function. My question is, can this be done in Access.

[alansidman]

Its not that we are not familiar with Index Match, rather it is Access is a relational database and excel is a spreadsheet. They do not operate in the same manner. There are many ways to seek out data within a RDBMS. It is primarily done with queries using SQL or using VBA extracting data from a recordset. So, depending on what you want to do, there probably is some way of getting the information you seek, but it will not be done with a formula such as Index-Match because database do not work in the same fashion as spreadsheets.

Again, I ask you to tell us what you want to do and we will help you to find the appropriate solution.

[praetorianprefect]

So how can you search a value by row and returning the data where the desired column and the search row intersect. Which is the principle of the function Index Match.

[alansidman]

You don't. Because RDBMS do not store records in a manner similar to a spreadsheet. The order of how data is stored is immaterial to how you extract data. Think of a DB as large garbage pail that you throw data into. It accumulates, but the order is not relevant. What is relevant is that data is stored in records and the records have fields. You extract the data through queries.

In a query, you would select your table and which fields you wish to extract data from. You would then set the criteria for the particular data you wish to extract. Based upon the fields selected and criteria, the results will be shown in datasheet that resembles what a spreadsheet looks like. That is the only similiarity in that they look alike.

This is a good read on the differences between databases and spreadsheets. They are a completely different mindset. I hope this white paper helps you to understand the difference.

http://forums.aspfree.com/microsoft-...el-349267.html