472,975 Members | 1,682 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,975 developers and data experts.

Using VLookUp in Excel to Link to Excel 'Tables'

32,548 Expert Mod 16PB
VlookUp or Vertical LookUp is a function that enables Excel to ‘Find’ related items from within a range stored elsewhere. Elsewhere can mean another excel workbook on another PC, but can also mean a range in the current worksheet.
In any Range reference in Excel, a ’$’ preceeding an element of the address, simply tells Excel not to be clever when copying or dragging the formula to other cells. Notice the formulas displayed in column B below: First B1 is created, then that cell is copied and pasted, or dragged down over, the other cells. Notice the only item that changes is the number (Row reference) after $A.
  1. This is the item to search for and is often a cell reference. The ‘$’ is usually required here for the column letter but rarely for the Row number.
  2. This is a reference to the range being searched. This is almost always fully referenced with ‘$’s.
    If it is a requirement to refer to ranges in other worksheets or even other workbooks, the format for each of these is illustrated in cells E1 & E3 below respectively. For space reasons, the range in Book2 starts at B3 rather than at E5, but ‘[Book1]Sheet 2’ has the data in the same place as Sheet1 (E5:F9). Basically, a worksheet reference preceeds the cell part with a ‘!’ separating them. Any complicated name (reserved word or containing unusual characters including spaces) requires singe quotes (‘) around it – Sheet2!$A$1:$C$9 but ‘Sheet 2’!$A$1:$C$9. A workbook reference preceeds the worksheet reference and is surrounded in [] brackets. If (‘)s required in sheet name then the workbook reference follows the first (‘) – ‘Book2]Sheet 2’!$A$1:$C$9.
  3. This is the column to display if a match is found. In the example below, the words (Abacus; Broadcast; …) are found in column F, but in column 2 from the left of the range. Column C shows the results displayed when the formula in Column B is used.
  4. This indicates to Excel how it should treat items where a match isn’t found :
    True (or non-zero) indicates it expects an ordered table and it will return the next item in the list after the point this item would have been. This is never used for simply finding items in a list. This would be used by accountants sometimes.
    False (or zero) will only find exact matches. Any item not found would return a #N/A value (See C4 below) which can be detected by using the ISNA() function.
Picture can be viewed or downloaded.

Attached Images
File Type: gif VLookup.Gif (31.8 KB, 412 views)
Jan 23 '07 #1
0 3938

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Fred | last post by:
I am trying to come up with a way to lookup values in Access as easily as I can in Excel. My application is looking up scores. An example lookup table below explains what I'm after: TABLE1...
by: Eddie | last post by:
I have an Access database that tracks project information. The database is very simple, except for 1 small aspect. Some background: 4 Tables - Project information, Employees, activity and pay...
by: Rebecca | last post by:
could someone tell me the correct syntax for using vlookup in visual basic
by: somanyusernamesaretakenal | last post by:
What I am trying to achieve: Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data) What I did was I...
by: catlover30 | last post by:
HI, I need help with excel VLOOKUP! I am at a new job and really trying to solve this without asking anyone at work, since they seem th expect me to know this. What I am trying to acheive is the...
by: scubasteve | last post by:
Looking up values from an Access table is simple. Simulating the 'Range Lookup' functionality from Excel's VLookup formula is a bit trickier. For those that aren't familiar with this, it allows...
by: sejal17 | last post by:
hello Can any one tell me how to read multiple worksheets from a single excel file.I have stored that excel in xml file.so i want to read that xml that has multiple worksheet.And i want to store...
by: John Brock | last post by:
I have a .NET application that, among other things, creates Excel workbooks, and I have run into a very strange problem involving formulas on one worksheet that reference values on another...
by: Monusonu | last post by:
Hi Expert, I am trying to get the value from excel formula cell using POI. My code works fine for less complex formula cells, but fails or returns error code for complex formula cells. Following...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.