Replicate MS Excel VLOOKUP Function in MS Access

I am converting several MS Excel spreadsheets into a single MS Access 2003 
database for an MS Excel User.


The MS Excel VLOOKUP query I am trying to recreate is as follows:

=IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE))



New MS Access Table Name:

0301_ElectricitySupply_FeatureLine-up


Field names for MS Excel and MS Access are as follows:

MS Excel field:                                   MS Access Field:
A2                                                    Verify Config

"A2" is VLOOKUP function listed above


MS Excel field:                                   MS Access Field:
B2                                                    Config No

Example: "B2" / "Config No": MTU0301-0010


MS Excel field:                                    MS Access Field:
EG2                                                   Concatenated Config No_4

Example: Cell "EG2" / Field "Concatenated Config No_4" data:
00200001000500010001000100010002000500010001000100030002000300010002000300010001000400040002000100010001000100020001


MS Excel field:                                    MS Access Field:
EH                                                     Concatenated Config 
No_4_2

Note: Column "EH" was created as a copy of "EG" for the User's VLOOKUP 
function to work properly.  As a result, "Concatenated Config No_4_2" is a 
duplicate of the "Concatenated Config No_4" field data.


MS Excel field: MS Access Field:
EI                                                      Config No_2

Note: The User created column "EI" as a copy of the "Config No" field data 
for his VLOOKUP function to work.


The way this is SUPPOSED to work…………

If a new "Config No" being entered has a unique "Concatenated Config No_4" 
data the "Verify Config" field should store "Good" in that field.

However, if there is an existing "Config No" record that contains a matching 
"Concatenated Config No_4" data the resulting "Verify Config" field should 
record the existing "Config No" instead of "Good" in the working form.  If 
there is no "Concatenated Config No_4" data to compare for the newly entered 
"Config No" record, "No Data" should be recorded in the "Verify Config" 
field; the VLOOKUP function in MS Excel lists "#N/A" in Column "A" when this 
happens.


Side Note: If either the "Config No_2" or the "Concatenated Config No_4_2" 
are not necessary for the lookup and compare functions in MS Access I would 
like to delete them if possible to clean up a lot of unnecessary data in the 
table.


I hope this was not too complicated to understand. It had to be explained to 
me several times for me to understand how the User's particular VLOOKUP 
function was supposed to work.


Any assistance would be GREATLY appreciated!!!


Thanks,

Chip Bender
MTU Detroit Diesel
0
Utf
3/8/2010 2:57:01 PM
access.formscoding 7493 articles. 0 followers. Follow

0 Replies
707 Views

Similar Articles

[PageSpeed] 47

Reply:

Similar Artilces:

VB6 formula not working in excel
Hi, I have a few formulas embedded in my vb6 code which calculate and enter the results in the excel sheet. These formulas work for me and everyone at my workplace. But for some reason these formulas have recently stopped working for some of our model users in other countries. I noted that the problem is only with the formulas that are calculating the percentage, the rest of the formulas are working on the sheet. I also wanted to ask, is there a difference in using N/A and N?A in VB6? I should probably ask this question in the VB forum, but if anyone knows, please advise. Thanks, ...

Excel spreadsheet in Reports only works within network
I have a dynamic Excel spreadsheet that dynamically updates the data when the spreadsheet is opened. However, it only works inside the network because of the SQL connection string. Is there a way to make this work when a user runs the spreadsheet from outside the network? The CRM dynamic Excel fetches the data from the CRM Database so needs to have connection to it. If someone from the outside of the network wants to be able to run this report, they need to have access to your CRM from their network, i.e. VPN Check with your IT, they maybe other network options to securely connect...

Information Transfer from Excel to Word
I am trying to transfer certain information from an Excel spreadsheet into particular sections of a Word document. The Excel document will be completed by a 3rd party and then I would like to run a macro against it from Word just to exctract information from certain individual cells and place it in a specific location in my Word document. I have a basic understanding of VBA and I am not sure how easy this is to complete. Has anyone got any ideas ? Thanks If you are just transfering data from a cell(s) to predetermined locations in a Word document, I might suggest that you Paste the co...

Replication
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3B410.C2879E10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I was told that Exchange 2003 has store replication a feature "out = of the box" that I would like to confirm. The company I work for is looking for a DR / BC plan. We are = evaluating software that will allow us to REAL TIME replicate the data = on one exchange server, to a standby server so that in the event of a = disaster, all we have to do is activate the standby server and we wil...

template class and static function pointer.
template <int tt> class aa { static void (* bb)(); }; template <int tt> void (* aa<tt>::bb)() = 0; what's wrong with this code? but it's not compiler in VC7.1! why?thanks! On 23 May 2007 17:31:04 -0700, madmanahong <madmanahong@163.com> wrote: >template <int tt> >class aa { > static void (* bb)(); >}; > >template <int tt> >void (* aa<tt>::bb)() = 0; > > >what's wrong with this code? > >but it's not compiler in VC7.1! > >why?thanks! My VC7.1 compiles it without complaint. -- Dou...

How to get rid of GETPIVOTDATA function?
Hello, while addressing cells in a spredsheet from PivotTable Excel 2007 always puts GetPivotData functionautomatically instead, like old versions, simply addressing cells (like =B3 or simillar). It makes my formulas very long and not possible to understand. I wonder if I can switch off this automatic functuion and tell Excel to address cells just by they simple address? Thanks Rafal Office button/Excel Options/Formulas/2nd section: deselect "Use GetPivotData functions..." "Rafal Hemmerling" <Rafal Hemmerling@discussions.microsoft.com> wrote in message news:...

Replication
We started replication on the hq database and are getting errors when we run the 401's on the customer and shipto tables. It seems to be picking up the replication column. Invalid column name 'msrepl_trn_version' anyone been succesfull with replication. any help would be greatly appreciated thanks hi Markd, Don't mind but if you have done this in live enviornment then it is worng, first of all no where it is written that it support the replication technology, I know i have put the replication script on the newsgroup for the store operation only becuase I have been us...

Import from Excel User specified Contact Information
Hello there How can I import contact information from MS Excel when I have may contact fields that are not standard in Outlook (and so do not appear in the import dialog)? Best regards, UT ut wrote: > Hello there > How can I import contact information from MS Excel when I have may > contact fields that are not standard in Outlook (and so do not appear > in the import dialog)? > Best regards, UT You will have to modify the spreadsheet so that the fields match that of Outlook. I'm afraid that won't work. Outlook doesn't support importing to a = custom fields. ...

OWA: Is There A Way to Add an Image to a Outlook Web Access 2003 Signature?
We are using Outlook 2003 on Exchange 2003, both SP2, on XP Server 2003. The images display as boxes with a red x inside of them. It works perfectly with Outlook 2003, but when we try to set up the signature in OWA, no dice. Any help would be greatly appreciated. Thank you very much in advance. Kevin In news:lof8q1pj2lfahar20u8olajq8chtfdf7a8@4ax.com, nospam@spamm.net <nospam@spamm.net> typed: > We are using Outlook 2003 on Exchange 2003, both SP2, on XP Server > 2003. The images display as boxes with a red x inside of them. It > works perfectly with Outlook 2003, but ...

Extracting Records From Excel Database
I have a 1000 row/15 column Excel database. One of the fields i "Project Description" where it decribes the nature of a capita project. I'm looking to find all records relating to "Fire an Security" which could include words such as "safety", "sprinkler" "Upgrade" "Fire" all within the Project Description field. I need t be able to pull all those records. How do I perhaps use Excel' advance filter feature to perform this search for what I may refer t as key words. Or, is there a worksheet function I can use to do th same. ...

Upgrade to Excel 2003 -Will not print!
I have upgraded to excel 2003 from works 7.0. It will not print. The "save" file window occurs. Even after saving, it will not print! John wrote: > I have upgraded to excel 2003 from works 7.0. It will not print. The "save" > file window occurs. Even after saving, it will not print! First thing to do is make sure the printer is working. Go to Start > Settings > Printers Right click on the printer and hit the Print Test page button. Are you hitting the print button or going to the file print option? The print button prints to your default printer make sure...

Export Function not Working
When I export from my financial institution into a Ms Money format or ofx file. It loads it into a temporary file folder but doesnot call up Money 2003 to load it and you cannot import this file directly from MsMoney once you have loaded it there. Any way of fixing this? Thanks You could try to reset the file association - see http://support.microsoft.com/default.aspx?scid=kb;en-us;327900 for instructions on how to do it (related to another problem). Alternatively, try renaming the temporary file to have an OFX extension and see if it imports (I would recommend doing this in a test fil...

Help with excel 2007 chart
Hey, this seems fairly simple but is stumping me. I have a table with static data that shows my budgetary costs per month for the last 2 years. The row labels in my table are the categories (mortgage, insurance, power, gas, phone, etc.) and the column labels are each month for 2007 and 2008. What I want to do is create a chart that will graph the data (line chart) for each category over the last two years. I would like to choose what category to display on the chart so they're not all on the chart at the same time. I have tried a Pivot Chart, but it's just not displaying the data corre...

Public Folder Replication error #4
We have windows 2000 domain, exchange 20003 running on windows 2003 member server, we have to windows 2000 sites, all AD replication is OK, but only public folder replication did not happen, between two servers, I am getting following NT event application event error EVENT ID 3093 Error -1603 reading property 0x36010003 on object type tbtFolder from database "First Storage Group\Public Folder Store Also I have following event 3085 Error 0x8004010f occurred while generating an outgoing replication message. Type: 0x4 Folder: (7-3ABC) Database "First Storage Group\Pub...

Using tree view in access
I don't know if what I'm attempting is possible in Access but here goes. I have an application in Access 2003 that uses two tabs (pages). On one I have text boxes, an unbound combo box and and an image control. I have set the path in code and linked one of the fields in the table to the image files so when the corresponding record is pulled up the image is displayed. On the second tab (page) I have placed a tree view control and would like for the end users to be able to scroll to the desired location on the network or designated drive (i.e. C:\images) and have the images...

Importing large *.TIFF image into Excel
I don't know if this is the right place to post this, but I have a large (3000x2000) TIFF image that I have cropped slightly (about 30x10) to remove the border. When I insert the original it works fine, however when I insert the cropped image it comes out all distorted. If I open up the cropped image in paint or Microsoft Picture Viewer it looks fine, its only in Excel and Word that this problem exists. ...

Help please
After a failed update to SQL 2008 SP1 + CU8, I restored master, and then tried to do a repair on the install to make sure I was back to SP1 only. I now get the following error: SQL Server 2008 - Developer Edition x64 Windows Server 2008 R2 x64 There was an error executing the Replication upgrade scripts TITLE: Microsoft SQL Server 2008 Setup ------------------------------ The following error has occurred: There was an error executing the Replication upgrade scripts. See the SQL Server error log for details. For help, click: http://go.microsoft.com/fwlink?LinkID=204...

Help for compare 2 data table in excel format!`
I got 2 data table in excel format, but I want to know the different between 2 table! For instance, Table 1, Table 2, Inventory ID Qty InventoryID Qty CC2210 5 AA1101 5 CC2211 8 CC2210 9 CC2212 100 CC2211 160 ...... Between 2 table, I want catch the inventoryID AA1101 IS THE DEFFERENT. How ahould I do ?? Thanks! Danny If you're looki...

Access 2007 "no current record" on split form
Hi, I am using VBA in Access 2007 to have a split form with a searchable text box. On new record entry the code will search for an existing entry in the recordset clone and if it exists will undo the control's changes and then set the form's bookmark to the found record's bookmark. This all work fine if the value is entered on the form half of the split form but if it is entered on the datasheet portion then I am given a "No current record" message box or a "Property not found" message box. It still finds the record but shows these message boxes after ...

Excel MVP
What is an Excel MVP and how do you become one? See: http://mvp.support.microsoft.com/ HTH Jason Atlanta, GA >-----Original Message----- >What is an Excel MVP and how do you become one? >. > Hi, Check this link http://tinyurl.com/3djab Ra -- Message posted from http://www.ExcelForum.com Hi Mark If you don't know what it is why do you want to be a MVP<g> Look here http://mvp.support.microsoft.com/ -- Regards Ron de Bruin http://www.rondebruin.nl "mark1" <anonymous@discussions.microsoft.com> wrote in message news:309f01c470e6$84943170$a3012...

IMAP replication
Hi all, First post to this newsgroup. I am wondering if it is possible to have Exchange sync itself with an IMAP server. We have an in house Exchange server that is used to manage Contacts, Calendars, and Tasks, however mail is handled by an external IMAP server. The ideal solution would be to have the two kept in sync, so any changes made on the Exchange server would reflect on the IMAP server and vice versa for each user. Is this just a pipe dream? Thanks, Hamlesh Motah. : We have an in house Exchange server that is used to manage : Contacts, Calendars, and Tasks, however mai...

IIf function
In my tblAddresses table I have a lookup field called TypeofAddress (Business, Delivery, Home, Mailing…) I am trying to tell it to display only one type out of the bunch: TypeofAddress 1. If TypeofAddress is not equaled to 1 then display TypeofAddress 2 and so on. I created a query using tblAddresses. The Criteria for TypeofAddress is: IIf([TypeofAddressID]=1,[TypeofAddressID],[TypeofAddressID]=2 Xor ([Address].[TypeofAddressID])=3 Xor ([Address].[TypeofAddressID])=7 Xor ([Address].[TypeofAddressID])=8 Xor ([Address].[...

Replicate Phone Message Form from Lotus Notes
Can you give me any suggestions for creating a phone message template. I have created forms in Word where you use a table and can type in information or use check boxes and can tab to the various fields. In this message form I would like to have multiple check boxes with different words after it like Telephoned, Please Call, Etc. and also a space for the Contact Name, and Phone Number. Lotus Notes has a form exactly like this. Any ideas on how to create this in Outlook. I tried copying it from Word but couldn't get it to work. Thanks. ...

Exchange issue on MS Cluster
Hi, Sorry for the long post, trying to make sure I have everything Background: Windows 2003 Cluster Exchange 2003 Cluster, singlee virtual server in Active/Passive config (3 node cluster) Additional SRS server Exchange 5.5 server Single site, single Org When the Exchange cluster was installed (new resource created), the Data path was configured as M:\Exchsrvr (located on shared SAN as physical disk) The server is currently not live (only has test users, public folder replicas, not primary RUS) It has been decided that they wish to remove Exchange from the M: drive and change the dri...

Help with formula for Microsoft excel
Hello, I am currently having some problems in getting a formula to work the way I want it to. Lets say in column a row a1 I have 716+70. In column a row a2 I would like 716+80 and in column a row a3 I want 716+90 etc for the remaining. What I would like is that it constantly add 10 to each row. Can someone help me out with this. Thanks Hi Mark in A1 =716+Rows($1:1)*10 Copy down as far as required -- Regards Roger Govier mark wrote: > Hello, > I am currently having some problems in getting a formula to work the way I > want it to. Lets say in column a row a1 I...