Excel Lookup/Search Tip 3 - Vlookup Explanation 2 - Vlookup to Search an Employee Database

Hi,
I have a database with values in this form;
Mass Intensity
10 123
11 69
12 789
13 566
14 8
15 158
... ...

and i want to search using another list of expected massesm to return the intensity of only the desired values. For instance;
Mass Intensity
12.1 789
13.9 8
...
so essentially i am using list2 to extract intensity values from list1. the problem is that the values don't match but are within a small range of the expected values say 0.2. vLookup won't work since it will match only the next smallest value and I can't seem to figure out the right criteria for dGet or if it will indeed work for this application.

Thanks,
Simon

Hi all, this is probably easy for you but I'm having problem with VLOOKUP and Visual Fox Pro database.

So here it is, the formula is correct and worked fine, until I specified the "Table array" of Fox Pro database, which I used MS Query to get it as an external database into Sheet 2, I also have to run VFPODBC.msi before I can link to it.

If I use this: =VLOOKUP(E5,Sheet2!A1:S8958,3,FALSE) then I got "#N/A" return. 32321 is in E5.

If I use this: =VLOOKUP("32321",Sheet2!A1:S8958,3,FALSE) then it worked fine, looking up "32321" in the Fox Pro database and return value 3 columns over. But this is a pain since there are many more rows to work with.

Please help

Thanks
Christopher

Excel VLOOKUP Multi-Array & Search Code Example
I've been working on this spreadsheet for the last couple of months.
I've changed the info but kept the formulas which you can extract for yourself. If you want me to answer any questions then please ask.

http://docs.google.com/fileview?id=0...NjE2ODBi&hl=en

It works on 98 and 03 but I haven't checked 07

Hi all,

I am new to the forum and a novice in excel so please bare with me and I appreciate your help.

I have a large list of product codes for which I need to make some product descriptions. The codes are fairly descriptive such as TN135CLC, which has the CLC standing for Cyan Low Capacity. I have been able to do this on a single basis but I want a formula that will cover all options such as TN135MLC (magenta low capacity) at the same time. Some of the codes have no C or M after. All possible options which come after the rest of the code are,

C (Cyan), M(magenta), K(black), Y(yellow), CLC(cyan low capacity), MLC(magenta low capacity) etc

The current formula is;

=IF(ISERROR(SEARCH("CLC",D29))," ","cyan low capacity")

In trying to add additional IF's, or add a VLOOKUP table of the codes and relivant text I am not able to get this to work.

Can someone help me complete this as it will save hours of work in typing out product descriptions?

I was not able to send a sample of my data so I hope the above is enough.

Many thanks

David

Greetings,

This i believe is my first post here so please bear with me.

I have a group of useforms for data input, the usage is for scheduling. I have one userform where the user will input data (date, time, customer, start address, end address, etc) this saves to one worksheet.

i have a second userform, this userform is used for organizing each day's schedule. i am using the microsoft Date/Time calendar to allow the user to select the date of the schedule they want to review.

the idea is to allow the user to select a date(using DTPicker1) and have the userform populate the rest of the userform's CheckBox and TextBoxes with the schedule information.

here are the issues i face;
- i want the userform code to on "Private Sub DTPicker1_Change()" search through the range on the worksheet and count the number of times the selected date(DTPicker1.Value) is found, then
- fill in the textboxes associated with the row data on the sheet.

i tried using vlookup but can't seem to get it to lookup a Date:
VB:

= Application.VLookup(CDate(DTPicker1.Value), Worksheets("Active").Range("gen_sch"), 1) 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



sidenote: the range gen_sch is not defined as a standard column:row; but uses OFFSET to bypass the header row. (not sure if that info is needed, but there ya go)

any ideas?

Hi, I just wanna create a database application with Access 2003 or 2007 to store and retrieve employee data for my company. I need help to see some sample databases as a model. Can anyone help me to find some databases?

Further, I want to make the database as an application to be installed. How do I do that in Access?

I have in Excel a list of my employees by Name/Race/Gender/Birthdate/Hire date/Salary. There are a couple things I want to do with this information but I can't find out how to do it in the help file or online. I have basic excel 2007 knowledge but nothing advanced. What I want to do is:

1. Find employees whose salary is between 50000 and 65000. (I'm assuming I can use some kind of formula?)

2. Find employees who are female and hired before a certain date, for example 1/1/99.

3. Find the number of employees in each race and income group.

This is what my table looks like (I changed names for privacy):

http://i238.photobucket.com/albums/f.../employees.jpg

Any help would be greatly appreciated.

Hello Experts:

I am using MS Excel 2007 on Win Xp. I have an excel sheet which have list of servers in column A starting from cell address A2.

I have a database instance from which I need to lookup for the server contact person and application contact person email address for each server listed there.

Below are the details of database and table from which I have to get the information:

Servername: CRBPRODKESP\SISP03
Authentication: Windows Authentication
Database name: Runbook database
Table names: dbo_QRB_AppSupport (this is for Application contact person email address) and dbo_QRB_Customer (this is for Server contact person email address)

Can any one please provide me with VBA code to extract this information.

Currently I have imported the tables to MS Access, created one input table and using below query to get this information and then copy and paste it to Excel.

Code:

SELECT InputServers.ServerName, dbo_QRB_Customer.EmailAddr, dbo_QRB_AppSupport.EmailAddr
FROM (InputServers INNER JOIN dbo_QRB_Customer ON InputServers.ServerName = dbo_QRB_Customer.ServerName) INNER JOIN dbo_QRB_AppSupport ON dbo_QRB_Customer.ServerName = dbo_QRB_AppSupport.ServerName;

I am after a code which I can run in Excel to find the Application and Server contact information from given Database/tables and fill the same next to the server name on the same sheet.

Hi All,

The 6 staff I employ send in their expenses every 2-4 weeks.

On the expense sheet is their name, employee number, as well as expense amounts under certain categories (e.g. food, travel, phone).

What I am wanting to do is create a macro which will have Access look into a folder to check for the existence of expense sheets.

If there are any I want it to import all of the totals from each column into the database, then move the files into a subfolder in the folder.

Is this a massive task?? Hints/tips appreciated.

Thanks.