470,861 Members | 1,789 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Using VLookUp in Excel to Link to Excel 'Tables'

32,311 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, 373 views)
Jan 23 '07 #1
0 3706

Post your reply

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

Similar topics

3 posts views Thread by Fred | last post: by
4 posts views Thread by Rebecca | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.