Using VLookUp in Excel to Link to Excel 'Tables'

[NeoPa]

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.