Function/Technique Similar to excel vlookup

[bobby_smith]

Hi all,
I'm hoping to find a function similar to excel vlookup and I'm also open to better solution if one is available.
I have a large set of data with location number and state for each location. I would like to verify that the location is in the correct state using an official location & state list.
In excel, I would perform vlookup of the two list and test if both match. The file is too large for excel and hence I would like to use Access. From my research, access does not have vlookup function, so I'm trying to find an alternative method.
The ultimate output that I need is a list of all locations which do not have the correct state, i.e. a listing of all locations which have a different state than whats on the official location and state list.

I would greatly appreciate any help or suggestions.

 

[tanny81]

Hi, I'd have thought the easiest solution would be to drop both data sets into separate tables and then use the Query Wizard in Access to get to the data you need. Access does have a function called DLookup, but you'd need to utilise that via vba.

 

[Micron]

I have a large set of data with location number and state for each location. I would like to verify that the location is in the correct state using an official location & state list.

Is there a connection between location number and official connection? That is, are they both of the same data type? Post some examples of your correct and not correct data (suggest you use Excel to easily create a nicely formatted table in your post - just copy and paste the cells from the spreadsheet after you center align all text). Your solution will likely involve some sort of query, depending on your tables. You can probably do this using the Unmatched Query wizard, finding values in the location list which do not match the "official" list.

DLookup does not have to be used in vba. It can be used in a form control or calculated query field to name two other ways.

 

[bobby_smith]

Hi Micron,
Just to provide some additional information that may clarify my original ask.
Access is not the original database of record. Its another software that holds the information. That software contains over 700,000 rows of data with over 30 different fields/columns. One of those columns contains the state information and another the location.
A location is associated with a specific state. There are approximately 5,000 unique locations ( store numbers).

What I'm trying to do is verify that the locations have the correct state in the database of record.
I have a master list with the 5,000 unique location(store numbers) and the correct state.

In excel, I would perform a vlookup using the location from the database of record to verify that the state listed in the database is the correct state for that location on the location and state master list.
Because there are over 700,000 rows, when the vlookup is perform, excel sometimes crashes.

I was wondering if this match/lookup could be done in Access.

 

[Micron]

There are no vlookup, hlookup or match functions in Access. After reading your second post, I'll say I haven't changed my guess as to how you'll have to perform your comparisons, assuming your 'official list' and the data to be checked is or can be put into tables.